Post reply

Problem with select query

  • December 3, 2009 at 5:02 pm

    #135792

    Hi Guys,

    I am having a small problem with the query which I have written.

    Here is the sample query :

    Declare @Mytable Table (ID Int, Code Int, Name varchar(10), ContactPerson Varchar(10), Amount money)

    Insert into @MyTable values(123, 8214, 'Raaj', 'Kevin', 200)

    Insert into @MyTable values(123, 8214, 'Raaj', 'George', 200)

    Insert into @MyTable values(124, 8000, 'Ram', 'Shane', 100)

    Insert into @MyTable values(128, 9000, 'VIJ', 'BOND', 500)

    Insert into @MyTable values(128, 9000, 'VIJ', 'STEVE', 500)

    --Select * from @MyTable

    The resultset looks like this :

    123 8214 Raaj Kevin 200.00

    123 8214 Raaj George 200.00

    124 8000 Ram Shane 100.00

    128 9000 VIJ BOND 500.00

    128 9000 VIJ STEVE 500.00

    But What I actually want the result set to look like is in this way:

    123 8214 Raaj Kevin, George 200.00

    124 8000 Ram Shane 100.00

    128 9000 VIJ BOND,STEVE 500.00

    AS you can see in the above result set when ID,CODE,NAME and AMOUNT have same values, I would like to show the CONTACTPERSON concatenated (seperated by comma) added to the previous which has the same values.

    Thanks,

    grkanth

  • December 4, 2009 at 1:00 am

    #1087520

    Does this help?

    Select ID, Code, Name,

    STUFF((Select ',' + ContactPerson

    FROM @mytable M2 where M2.ID = M1.ID for xml path('')), 1,1 ,'')

    Amount

    from @MyTable M1

    Group by ID, Code,Name, Amount

    ---------------------------------------------------------------------------------

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply