Comma Separated in a single result (interesting request)

• npatel565

  SSC Veteran

  Points: 202

  More actions

  May 30, 2014 at 9:15 am

  #289322

  Good day forumites, I have a unique request here.

  I have the following table

  CREATE Table #Table1

  (

  ID INT, Name VARCHAR(50), Class VARCHAR(10)

  )

  INSERT INTO #Table1

  Select 1, 'name1', 'a' UNION ALL

  Select 2, 'name1', 'a' UNION ALL

  Select 3, 'name2', 'b' UNION ALL

  Select 4, 'name2', 'b' UNION ALL

  Select 5, 'name2', 'c' UNION ALL

  Select 6, 'name2', 'c' UNION ALL

  Select 7, 'name2', 'd' UNION ALL

  Select 8, 'name2', 'd' UNION ALL

  Select 9, 'name3', 'e' UNION ALL

  Select 10, 'name3', 'f' UNION ALL

  Select 11, 'name3', 'f' UNION ALL

  Select 12, 'name3', 'f'

  Is it possible to have each name and its corresponding class in a single line separated by commas to give a result like the one below in #table2 ?

  CREATE Table #Table2

  (

  ID INT, CommaSeparated VARCHAR(100)

  )

  INSERT INTO #Table2

  Select 1, 'name1, a' UNION ALL

  Select 2, 'name2, b, c, d' UNION ALL

  Select 3, 'name3, e, f' UNION ALL

  Select 4, NULL UNION ALL

  Select 5, NULLUNION ALL

  Select 6, NULLUNION ALL

  SELECT 7, NULLUNION ALL

  SELECT 8, NULLUNION ALL

  SELECT 9, NULLUNION ALL

  SELECT 10, NULLUNION ALL

  SELECT 11, NULLUNION ALL

  SELECT 12, NULL

  what I have

  Select * FROM #Table1

  Final Result

  Select * FROM #Table2

  Note that I still want to see all the IDs regardless.

  If that is not possible to see all the IDs, I think the results below in #Table3 should suffice.

  CREATE Table #Table3

  (

  CommaSeparated VARCHAR(100)

  )

  INSERT INTO #Table3

  Select 'name1, a' UNION ALL

  Select 'name2, b, c, d' UNION ALL

  Select 'name3, e, f'

  Select * FROM #Table3

  Thanks for your time.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 30, 2014 at 9:29 am

  #1717837

  That's a really weird requirement. I can't see why would you want to do this but here's an option.

  WITH ConcValues AS(

  SELECT ROW_NUMBER() OVER( ORDER BY Name) AS rn,

  CommaSeparated = Name + CONVERT(varchar(max),(SELECT ', ' +Class

  FROM #Table1 x

  WHERE t1.Name = x.Name

  GROUP BY Class

  ORDER BY Class

  FOR XML PATH('')))

  FROM #Table1 t1

  GROUP BY Name

  )

  SELECT t1.ID,

  cv.CommaSeparated

  FROM #Table1 t1

  LEFT

  JOIN ConcValues cv ON t1.ID = cv.rn

  For the comma separated values, check the following article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• gbritton1

  SSCertifiable

  Points: 6522

  More actions

  May 30, 2014 at 9:51 am

  #1717855

  Here's another way:

  with cte as (

  select rn=ROW_NUMBER() over(order by name)

  , CommaSeparated = max(t1.name + t2.classlist)

  from #table1 t1

  cross apply (

  select ', ' +t2.class

  from #table1 t2

  where t1.name = t2.name

  group by class

  for xml path('')) t2(classlist )

  group by t1.name

  )

  select * from cte

  union all

  select top((select count(*) from #table1) - (select count(*) from cte))

  ROW_NUMBER() over(order by (select 1))+(select count(*) from cte)

  , null

  from #Table1

• npatel565

  SSC Veteran

  Points: 202

  More actions

  June 3, 2014 at 1:16 pm

  #1718658

  The first solution worked correctly. Thank you guys for both solutions.