SQL: Make a Comma Separated String, featuring XML!
Author: jason
Date: 2014-01-28 15:10:29
Category: Musing

A frequent question is how to turn rows into a comma separated string. We can use the power of XML with an Expression and a Function to twist the output into a string.





In our example we really don't need the COALESCE or STUFF function. If we just use the FOR XML PATH('') statement we get the below result as XML. We are simply adding a Comma (which is inside the apostrophes) to the name column value. The empty path in the FOR XML will just return our values with no element/node/attribute type markups.




We need to use COALESCE if we want to account for NULL results and instead return a blank string. Also if we don't want the 1st comma listed we need to use STUFF. In this example STUFF starts our string from position 1, goes over 1 position, and replaces whatever that value is (the leading comma) with a blank string.




The STUFF Function inserts a string into another string: http://technet.microsoft.com/en-us/library/ms188043.aspx

The COALESCE Expression returns first non null value: http://msdn.microsoft.com/en-us/library/ms190349.aspx

Code

DECLARE @Temp AS TABLE ([id] BIGINT IDENTITY(101,1), [name] VARCHAR(10));
INSERT INTO @Temp VALUES ('Apple'), ('Apple'), ('Peach'), ('Pear');

SELECT
COALESCE(
STUFF(
(SELECT ',' + name AS [text()]
FROM @Temp
FOR XML PATH(''))
, 1, 1, '')
, '')
AS MyString




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009