none
how to concat a duplicate column values to show as a single column value rather than just max and group by please...

    Question

  •  

    Hello Sir,


    SELECT  

    i.itemnum AS RequestId,
    i.itemdate AS DocumentHandleDate,
    AccountID_KX.keyvaluesmall AS AccountId,
    AccountNumber_KX.DataValue AS AccountNum,
    REPAccountNumber_KX.DataValue AS RepAccNumber,
    Currency_KX.DataValue AS Currency,
    AccountStatus_KX.DataValue AS AccountStatus

    FROM         thirdpropertyDBitemdata AS i LEFT OUTER JOIN
                          thirdpropertyDBkeyitem353 AS AccountID_KI ON i.itemnum = AccountID_KI.itemnum LEFT OUTER JOIN
                          thirdpropertyDBkeyxitem354 AS AccountNumber_KX ON i.itemnum = AccountNumber_KX.itemnum LEFT OUTER JOIN
                          thirdpropertyDBkeyxitem355 AS REPAccountNumber_KX ON i.itemnum = REPAccountNumber_KX.itemnum LEFT OUTER JOIN
                          thirdpropertyDBkeyitem146 AS Currency_KI ON i.itemnum = Currency_KI.itemnum LEFT OUTER JOIN
                          thirdpropertyDBkeyxitem356 AS AccountStatus_KX ON i.itemnum = AccountStatus_KX.itemnum

     

    by issueing the above query i am getting correct data, but duplicated currency (i.e., same row with just different currency)

    so to eliminate that i used

    SELECT  

    i.itemnum AS RequestId,
    i.itemdate AS DocumentHandleDate,
    AccountID_KX.keyvaluesmall AS AccountId,
    AccountNumber_KX.DataValue AS AccountNum,
    REPAccountNumber_KX.DataValue AS RepAccNumber,
    max (Currency_KX.DataValue) AS Currency,
    AccountStatus_KX.DataValue AS AccountStatus

    FROM         thirdpropertyDBitemdata AS i LEFT OUTER JOIN
                          thirdpropertyDBkeyitem353 AS AccountID_KI ON i.itemnum = AccountID_KI.itemnum LEFT OUTER JOIN
                          thirdpropertyDBkeyxitem354 AS AccountNumber_KX ON i.itemnum = AccountNumber_KX.itemnum LEFT OUTER JOIN
                          thirdpropertyDBkeyxitem355 AS REPAccountNumber_KX ON i.itemnum = REPAccountNumber_KX.itemnum LEFT OUTER JOIN
                          thirdpropertyDBkeyitem146 AS Currency_KI ON i.itemnum = Currency_KI.itemnum LEFT OUTER JOIN
                          thirdpropertyDBkeyxitem356 AS AccountStatus_KX ON i.itemnum = AccountStatus_KX.itemnum

    group by
    i.itemnum,
    i.itemdate,
    AccountID_KX.keyvaluesmall ,
    AccountNumber_KX.DataValue,
    REPAccountNumber_KX.DataValue,
    AccountStatus_KX.DataValue


    but with this i am getting just one currency

    but how can i have all currencies as a concatinating rather than having just max of currency

    so my expected result will be get only unique rows, if duplicated by currency then curreny all currenies will be added to (concatinate) to show as a single value

    please help me this

    i tried a lot but no ideas i came up with is it possible or not

    thanks in advance
    asitti

    Wednesday, August 03, 2011 3:20 PM

Answers

All replies

  • If you're using SQL 2005 and up, then you can concatenate rows using FOR XML PATH('') technique. Take a look at the following links explaining the problem in details:

    MSDN thread about concatenating rows

    Making a list and checking it twice

    Concatenating Rows - Part 1

    Concatenating Rows - Part 2


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Surendra Nath GM Wednesday, August 03, 2011 3:43 PM
    • Marked as answer by asitti7 Wednesday, August 03, 2011 5:06 PM
    Wednesday, August 03, 2011 3:24 PM
    Moderator
  • here is nice article to concatenate multiple rows http://www.projectdmx.com/tsql/rowconcatenate.aspx

    http://uk.linkedin.com/in/ramjaddu
    Wednesday, August 03, 2011 3:27 PM
  • Hello Naomi,

     

    i went through your first link thread msdn question that is with examples i did complete my work but i have to know more how this works etc...

     

     

    thanks again naomi,

     

    you saved me a lot of times

    i greatful to you naomi

    Wednesday, August 03, 2011 5:06 PM
  • thanks ram

     

    very fine materials, for now i am using naomi directed thread solution to get my answer, i will go through your link it got good excellent value of information about concatination

     

    thanks again ram

    asitti

    Wednesday, August 03, 2011 5:07 PM