SQL – How to concatenate/combine column values into comma separated text string

by

The following SQL statement shows how to combine a column into a single text string – separated by a comma.

For example:

Name
Fred
Jim
Jane
Betty

Would be combined as:

Fred, Jim, Jane, Betty

To use, replace Name with the name of your column and table with the name of your table.

SELECT Stuff(
  (SELECT N', ' + Name FROM table FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

If you need to filter the values before combining you can add the WHERE statement after the table name, for example to only show names starting with a J:

SELECT Stuff(
  (SELECT N', ' + Name FROM table WHERE Name like 'J%' FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

 

Reference: http://stackoverflow.com/a/3672860/1318464