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.
October 30, 2008 at 1:39 pm
Awesome. Just what I was looking for.
November 18, 2008 at 5:25 am
Very helpful post!
March 14, 2009 at 8:33 am
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
March 20, 2009 at 2:33 pm
Ya it’s working…….
Thanks
September 2, 2009 at 1:22 am
Good Post! Thanks
September 10, 2009 at 1:29 pm
wow … i was looking for this ….:)
thanks
October 14, 2009 at 2:27 pm
Thank you very much.
It very good ^^
October 15, 2009 at 10:53 am
I could only create a list of varchar elements, what about a list of int?
October 23, 2009 at 8:39 am
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
November 7, 2009 at 1:53 am
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)