Aggregate to CSV

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.

This example was created based on work in this thread:
http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx

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

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:

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