Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

SQL Column to Comma Separated List
Published: Sep 26, 2014
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).
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.