Every so often I have the need to concatenate a column of strings returned from a SQL query. I have always used the COALESCE function to do this, something like the following:
SELECT @names = COALESCE(@names + ',', '') + FirstName FROM Residents
I looked up the documentation for COALESCE and realized that it is supposed to be used to return the first nonnull expression among those passed to it. While this works for concatenation, using it in such a fashion is more of a hack rather that what it is actually supposed to be use for.
With the introduction of SQL Server 2005, we are able to combine both SQL and XML much easier. Among the new features is the FOR XML PATH function that is intended for string concatenation when generating XML.
SELECT ', ' + FirstName FROM Resident FOR XML PATH ('') -- Output -- ------ -- , Shireda, William, Melissa, Kelly, Clarence
To get rid of the leading comma and space we can use the STUFF function. From MSDN:
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
STUFF (character_expression , start , length ,character_expression)
The final code becomes:
SELECT STUFF((SELECT ', ' + FirstName FROM Resident FOR XML PATH ('')), 1, 2, '') -- Output -- ------ -- Shireda, William, Melissa, Kelly, Clarence
Some may argue that using the FOR XML PATH function for string concatenation is just as much as a hack as using the COALESCE function as we aren’t actually generating XML but using the FOR XML PATH function seems a bit more intuitive to me.