Aggregate to CSV in TSQL

Sometimes we need to rollup string values into lists of comma separated values. Using “FOR XML PATH” per column that you want to rollup can provide a way to do this.

Let’s say you have a data set like so:

id	fruit	city
101	Apple	Cleveland
101	Apple	Pittsburgh
101	Banana	Pittsburgh
102	Grape	Cleveland
102	Melon	Cleveland
103	Melon	Pittsburgh
103	Melon	Cleveland

and for each distinct “id”, you want a list of its distinct fruits and cities.

If you put a FOR XML PATH statement for each column you want to rollup like so:

...
----------fruit---------
,STUFF((SELECT DISTINCT ', ' + NULLIF(fruit,'')
FROM tableToCSVRollup t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS fruit
...

and then GROUP BY id, you’ll have a nice aggregated set. It will even put it in alphabetical order and remove NULL and empty string values for you.

Complete code example:

;WITH tableToCSVRollup (id,fruit,city)
AS 
(
			  SELECT 101, 'Apple', 'Cleveland'
	UNION ALL SELECT 101, 'Apple', 'Pittsburgh'
	UNION ALL SELECT 101, 'Banana','Pittsburgh'
	UNION ALL SELECT 102, 'Grape', 'Cleveland'
	UNION ALL SELECT 102, 'Melon', 'Cleveland'
	UNION ALL SELECT 103, 'Melon', 'Pittsburgh'
	UNION ALL SELECT 103, 'Melon', 'Cleveland'
)
SELECT
id
----------fruit---------
,STUFF((SELECT DISTINCT ', ' + NULLIF(fruit,'')
FROM tableToCSVRollup t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS fruit
----------city----------
,STUFF((SELECT DISTINCT ', ' + NULLIF(city,'')
FROM tableToCSVRollup t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS city
------------------------
FROM tableToCSVRollup t1
GROUP BY id
ORDER BY id

Results:

id	fruit	city
101	Apple, Banana	Cleveland, Pittsburgh
102	Grape, Melon	Cleveland
103	Melon	Cleveland, Pittsburgh

——————————————