Link to home
Start Free TrialLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

asked on

T-SQL convert rows to comma-separated string, with a GROUP BY

Hello my under-apprecitated SQL genius buddies..

I have a two-column SQL 2012 table (disguised to protect the innocent), one of which is a group name, and one is numbers
IF OBJECT_ID('tempdb..test') IS NOT NULL
   DROP TABLE #test
GO

CREATE TABLE #test (
   group_name varchar(10), 
   id int
)

insert into #test (group_name, ID) 
values 
   ('red', 101), ('red', 102), ('blue', 103), ('blue', 104), ('hazel', 105), ('mauve', 106),
   ('grape', 107), ('red', 108), ('blue', 109), ('purple', 110), ('grape', 111), ('red', 112)

Open in new window

I need to generate some T-SQL, using FOR XML PATH I think but am not sure, to group by the group_name column, and return all of the ID's as a comma-separated string, like...

group_name    id_list
red              101, 102, 108, 112
blue            103, 104, 109
hazel          105
mauve        106,
grape          107, 111
purple        110

Thanks in advance.
Jim
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn

ASKER

Looks like a winner.  Thanks.
Avatar of Ashutosh` Pandey
Ashutosh` Pandey

Thanks...Its working Fine