SQL Column to Comma Separated List
Published: 25th July 2013
This is a handy little “trick” which I use all the time when I need to turn the column of a table into a comma separated list. It’s incredibly quick, has very little overhead, and it’s effective.

One of the main uses I have for this is when I want to dynamically create a select statement across a table of unknown width. With just a little help from sys.objects and sys.columns it can quickly become an invaluable piece of code to have up your sleeve.

Let’s start with an example of turning a column of data into a comma separated list:

Firstly we’ll create a simple table and place some data into it…

if object_id('tempDB..#table') is not null drop table #table

create table #table
(
  
id tinyint identity(1, 1),
  
fullName varchar(40)
)
insert into #table
values('Homer Simpson'),
       (
'Marge Simpson'),
       (
'Lisa Simpson'),
       (
'Bart Simpson'),
       (
'Maggie Simpson')


Now, to get the desired result requires a variable to hold the results, and a nifty little trick with T-SQL…

declare @csvList varchar(max)

select @csvList = isnull(@csvList + ', ', '') + fullName
from #table
order by id

select @csvList


And this is the result…

It really is as simple as that.

My most common use for this is as a column list for an unknown table, so let’s create a random table with a good few columns…

if object_id('myTestTable') is not null drop table myTestTable

create table myTestTable
(
  
id tinyint identity(1, 1),
  
firstName varchar(15),
  
lastName varchar(20),
  
title varchar(10),
  
address1 varchar(100),
  
address2 varchar(100),
  
country varchar(50),
  
planet varchar(5),
  
telephone varchar(10)
)
-- you get the idea...  random table with many columns


Now, say that you wanted to dynamically generate a list of columns to use in an insert or select; well, it’s simple…

declare @csvList varchar(max)

select @csvList = isnull(@csvList + ', ', '') + c.name
from sys.objects o
join sys.columns c
on o.object_id = c.object_id
where o.name = 'myTestTable'
order by c.column_id

select @csvList


And we’re done…

And don’t think you’re limited to a comma, you can use anything you want in there. You can even append information to the columns if required. It’s incredibly flexible and powerful when used properly. I hope you get as much use out of it as I have as I use it in numerous dynamic situations (such as my sp_merge procedure).
Comments:
NB: Comments will only appear once they have been moderated.