Obtaining a comma delimited list for each group in the output of a Group By query

• rsryanstephen

  Newbie

  Points: 8

  More actions

  January 10, 2014 at 2:19 pm

  #287202

  Hi, I'd like to ask how you would get the OUTPUT below from the TABLE below:

  TABLE:

  id category

  1 A

  2 C

  3 A

  4 A

  5 B

  6 C

  7 B

  OUTPUT:

  category count id's

  A 3 1,3,4

  B 2 5,7

  C 2 2,6

  The code would go something like:

  Select category, count(*), .... as id's

  from TABLE

  group by category

  I just need to find that .... part

• Lowell

  SSC Guru

  Points: 323573

  More actions

  January 10, 2014 at 2:31 pm

  #1679325

  here's a testable solution you can adapt to your real data;

  this gets each table, and a comma delimited list of column names.

  use this as a model, and change it to match your data:

  SELECT DISTINCT

  t.name,

  sq.Columns

  FROM sys.tables t

  JOIN (

  SELECT OBJECT_ID,

  Columns = STUFF((SELECT ',' + name

  FROM sys.columns sc

  WHERE sc.object_id = s.object_id

  FOR XML PATH('')),1,1,'')

  FROM sys.columns s

  ) sq ON t.object_id = sq.object_id

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• rsryanstephen

  Newbie

  Points: 8

  More actions

  January 13, 2014 at 5:48 am

  #1679550

  Thanks Lowell, but wow that code is way beyond me.. I'm still noob at SQL

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  January 13, 2014 at 7:18 am

  #1679575

  ;WITH result (category,ids) AS (

  SELECT category,

  STUFF((SELECT ',' + CAST(b.id as varchar(10))

  FROM b

  WHERE b.category = a.category

  FOR XML PATH('')),1,1,'')

  FROM a

  GROUP BY category)

  SELECT category,(LEN(ids)-LEN(REPLACE(ids,',','')))+1 AS [count],ids

  FROM result

  ORDER BY category ASC

  Far away is close at hand in the images of elsewhere.
  Anon.