SQL Server CSV From Data

A powerful way to convert row-level data to CSV

The ability to convert row-level data into CSV values can be exceedingly useful.

Using the STUFF function in combination with the FOR XML PATH('') command is a powerful way of achieving this.

DECLARE @OUTPUT VARCHAR(1000)
SELECT @OUTPUT = STUFF(
  (SELECT ',' + MyStringVal
   FROM MyTable
   WHERE MyIntVal = SomeIntVal
   FOR XML PATH('')
  ), 1, 1, '')

The STUFF function "inserts a string into another string", and in this case is purely used to remove the leading comma.

The FOR XML PATH('') is the key, and takes row-level data and turns it into a string of XML data. By using PATH('') we're telling SQL to not wrap the XML with a root node.

Finally, the use of ',' + MyStringVal results in the column having no name... which means SQL will not wrap the individual values in an XML node either.