T-SQL Hack to Turn a Set of Row Values into a Delimited String

Ever want to turn multiple rows into a comma separated list when you are querying your database?

Here’s a quick way to get it done using “FOR XML PATH(”)”:

 
DECLARE @comma_delimited_list VARCHAR(6000)
SELECT @comma_delimited_list =
(
SELECT productName + ','
FROM Products
WHERE productName LIKE 'a%'
FOR XML PATH('')
)
 
IF LEN(@comma_delimited_list) > 0
SELECT @comma_delimited_list = STUFF(@comma_delimited_list ,LEN(@comma_delimited_list), 1, '')

A couple things to remember: 1) you have set your path to ” and 2) you can’t name your selection or it will put the name into your xml adding more information than you want.

This entry was posted in MS SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *