troubleshooting Question

Comma delimited list, tsql

Avatar of crompnk
crompnkFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server 2005SQL
2 Comments1 Solution479 ViewsLast Modified:
Hi,
I would like to amalgamate values from multiple rows into a comma delimited list but group by another column (key), so for example the outcome would be:

Name     Group
Address  Home,Work
City         Home,Work

CREATE TABLE TEMPTABLE(

             [NAME] [varchar] (20),
             [GROUP] [varchar] (20)
             )
GO
INSERT INTO TEMPTABLE([NAME], [GROUP]) VALUES ('Address','Home')
INSERT INTO TEMPTABLE([NAME], [GROUP]) VALUES ('City','Home')
INSERT INTO TEMPTABLE([NAME], [GROUP]) VALUES ('Address','Work')
INSERT INTO TEMPTABLE([NAME], [GROUP]) VALUES ('City','Work')
GO

SELECT [NAME]
FROM TEMPTABLE

DROP TABLE TEMPTABLE

SELECT List = STUFF((
			SELECT DISTINCT ', ' + QUOTENAME([GROUP], '''')
			FROM (SELECT * FROM TEMPTABLE) x
			FOR XML PATH('')
				,TYPE
			).value('.', 'VARCHAR(8000)'), 1, 3, '')
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros