Recently I needed to convert a column to a file of comma separated values (CSV). There are a few ways to do this via TSQL, here’s a simple one – use COALESCE. The idea is to declare a variable, set it equal to itself and a comma and the next data value, finally select the variable. See the example as it’s much easier to follow.
Create table CSVTest (Name varchar(50)) GO Insert CSVTest Values('Andy Lohn'), ('Adrian Lohn'), ('Maggie Lohn'), ('Nala Lohn') Go Select Name from CSVTest Go
This returns, as expected the following:
Declare @Names varchar(2000) Select @Names = Coalesce(@Names + ', ','') + Name from CSVTest Select @Names as 'CSV List'
This returns the same results, except in as a comma separated list instead of a column.
Here are some related posts from the intraweb – including other ways to do this with user defined functions and XML:
- A post from Michael Freidgeim’s Blog here: T-SQL User defined function to concatenate column to CSV string
- A different method here using XML from Pinal Dave: SQL SERVER – Comma Separated Values (CSV) from Table Column