Problem with select query

• grkanth81

  Ten Centuries

  Points: 1194

  More actions

  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 :

  1238214RaajKevin200.00

  1238214RaajGeorge200.00

  1248000RamShane100.00

  1289000VIJBOND500.00

  1289000VIJSTEVE500.00

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

  1238214RaajKevin, George200.00

  1248000RamShane 100.00

  1289000VIJBOND,STEVE500.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

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  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

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