Generating a Comma-Delimited String from Rows in SQL Server – Andrey Zavadskiy

I had to solve the task how to make a string of values from a database table because JQuery UI plugin accepts only a comma-delimited string. I used a XML solution suggested by Anith Sen in his article Concatenating Row Values in Transact-SQL

SELECT STUFF(
  (
    SELECT ',' + [field_name]
    FROM [table_name]
    FOR XML PATH(''), TYPE
  ).value('.', 'varchar(max)'),
  1, 1, '') AS [string_of_values]

SELECT STUFF(

(

SELECT ',' + [field_name]

FROM [table_name]

FOR XML PATH(''), TYPE

).value('.', 'varchar(max)'),

1, 1, '') AS [string_of_values]

All you need is to replace text in square brackets with actual names. If you need another separator just change the comma char in the 3rd row to something else.

And finally there’s a screenshot of working example.

UPDATE 2016-04-13:

Here is another great article about different ways how to make group concatenation and performance comparison http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation

Leave a Reply Cancel reply