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, '')
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.