This weeks tip:
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.
The Setup:
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:
The Example:
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