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 (Table)

• 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.