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