I haven’t been blogging enough lately, so I’m going to try something new to get past this mild case of writer’s block. Instead of trying to do a long, in-depth post on some esoteric subject, I’m going to just put out a simple SQL script. This one’s a bit of a hack, but you’ll see a lot of variations on it: Coalescing rows using for XML. For example, here’s a few questions from StackOverflow:
How to Comma separate multiple rows obtained from a SQL Query
Convert multiple rows into one with comma as separator
The basic challenge is to denormalize your resultset so that multiple rows get returned as a single delimited string; while there are several methods for solving this problem, the one that I like (as of SQL 2005) is to use FOR XML PATH, like so:
BEGIN TRAN
DECLARE @t TABLE ( fruit VARCHAR(10) )
INSERT INTO @t
( fruit
)
SELECT 'apple'
UNION ALL
SELECT 'banana'
UNION ALL
SELECT 'tomato'
SELECT ',' + fruit
FROM @t
FOR XML PATH
SELECT ',' + fruit
FROM @t
FOR XML PATH('')
SELECT STUFF(( SELECT ',' + fruit
FROM @t
FOR
XML PATH('')
), 1, 1, '') AS fruits
ROLLBACK
The multiple results sets are there to illustrate each step in the process; for example, the first output shows what happens when you specify FOR XML PATH with no indicators; SQL Server automatically assigns each row in the resultset to a node named <row>:
<row>,apple</row>
<row>,banana</row>
<row>,tomato</row>
If you specify a tag name in the PATH function, each row will be replaced with that tag name; in the above example, we want an empty tag (‘’). The result is an XML fragment with no tags:
,apple,banana,tomato
That’s almost what we want, except we need to get rid of the leading comma. The STUFF command basically replaces the characters in the string supplied (the first parameter; our query FOR XML PATH) starting at a certain position (1 in the example above) and moving forward the specified number of character places (1, the third parameter) with the value of the last parameter (an empty space; the fourth parameter). The final output is a comma delimited string:
apple,banana,tomato
Straightforward; gotta return to writing.
