coalesce (or alternative) PIVOT *into* CSV

• dandenson

  Mr or Mrs. 500

  Points: 566

  More actions

  April 7, 2011 at 2:36 pm

  #249110

  I can't figure out how to add a 'grouping' to the output of this. any help appreciated. Thanks

  current data

  name , SNUM

  bill , 1

  bill , 2

  tom , 3

  tom ,20

  john , 6

  current code:

  DECLARE @SNUMS varchar(max)

  select @SNUMS = COALESCE(@SNUMS,'') + data.SNUM + ','

  from (

  select distinct SNUM from TIMSDATA.dbo.SALMAN

  ) data

  select @SNUMS

  current output:

  '1,2,3,6,20'

  desired output

  name, snums

  'bill','1,2'

  'tom','3,20'

  'john','6'

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  April 7, 2011 at 3:52 pm

  #1308869

  dandenson (4/7/2011)

  desired output

  name, snums

  'bill','1,2'

  'tom','3,20'

  'john','6'

  Hi,

  This is what you are looking for

  go

  declare @Temp table(name varchar(50),Snum varchar(50) )

  insert @Temp values

  ('bill' , 1),

  ('bill' , 2),

  ('tom' , 3),

  ('tom' ,20),

  ('john' , 6)

  SELECT DISTINCT name,SUBSTRING((SELECT ','+Snum AS [text()] FROM @Temp WHERE name = T.name ORDER BY Snum FOR XML PATH( '' ) ), 2,200) AS J

  FROM @Temp AS T;

  Thanks
  Parthi