Convert table to CSV string in SQL Server
There doesn’t seem to be a native function in SQL Server to collapse a table of row values into a comma-separated string, for example:
Animal
Llama
Manatee
Pygmy Marmoset
Okapi
Result CSV: “Llama, Manatee, Pygmy Marmoset, Okapi”
In mySQL there’s a built-in aggregate function called group_concat, but no equivalent in SQL Server unless you build your own .NET function, like in this TechNet article Invoking CLR User-Defined Aggregate Functions. That’s quite a chunk of coding and is restricted to SQL Server 2005 or later, so here’s a handy SQL snippet that does a similar job without the fuss.
select Name from Animal
declare @csv varchar(max)
select @csv = coalesce(@csv + ‘, ‘, ”) + Name from Animal
select @csv
If you use the code regularly then consider creating a scalar user-defined function (UDF) that returns the CSV string as varchar.
about 1 year ago
Awesome. Just what I was looking for.
about 1 year ago
Very helpful post!
about 1 year ago
Thank you very much for your post! This is a very clean way to convert the data in a table column into a single string.
For others reviewing this post, note that the string being appended to @csv above -> ‘, ‘, ” is:
1) a single quote
2) a comma
3) a space
4) a single quote
5) a comma
6) a space
7) two single quotes
NOTE: single quotes are used to delimit strings in SQL
about 1 year ago
Ya it’s working…….
Thanks
about 1 year ago
Good Post! Thanks
about 11 months ago
wow … i was looking for this ….:)
thanks
about 10 months ago
Thank you very much.
It very good ^^
about 10 months ago
I could only create a list of varchar elements, what about a list of int?
about 10 months ago
SUUUUPER handy, I never would have thought of that. Thanks a lot.
Amanda: I too need a list of ints so I’ve modified it a bit to turn the ints into strings
select @csv = coalesce(@csv + ‘, ‘, ”) + LTRIM(STR(AnimalID)) from Animals
about 10 months ago
Here’s a faster variant of the code you gave:
DECLARE @Csv NVARCHAR(MAX)
SET @Csv = ”
SELECT @Csv = @Csv + COALESCE(Name, ”) + ‘,’
FROM Animals
SET @Csv = SUBSTRING(@Csv 1, LEN(@Csv) – 1)
SELECT @Csv
…and another even faster, albeit hacky variant:
DECLARE @Csv NVARCHAR(MAX)
SELECT @Csv =
(
SELECT COALESCE(Name, ”) + ‘,’
FROM Animal
FOR XML PATH(”)
)
SET @Csv = SUBSTRING(@Csv, 1, LEN(@Csv) – 1)
SELECT @Csv
(I’m using SQL Server 2005)
about 9 months ago
What if the list has null value?
about 8 months ago
COALESCE(Name, ”) return Name or empty string if Name is null. check http://msdn.microsoft.com/en-us/library/ms190349.aspx