Comma delimited list, tsql
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
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 TEMPTABLESELECT List = STUFF((
SELECT DISTINCT ', ' + QUOTENAME([GROUP], '''')
FROM (SELECT * FROM TEMPTABLE) x
FOR XML PATH('')
,TYPE
).value('.', 'VARCHAR(8000)'), 1, 3, '')ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
, STUFF(
(SELECT ', ' + QUOTENAME([GROUP], '''')
FROM TempTable
WHERE Name = R.Name
ORDER BY [Group]
FOR XML PATH('')
)
, 1
, 2
, '') AS List FROM TempTable AS R