Jim Horn
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
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
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks...Its working Fine
ASKER