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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: