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.

Your SQL Trainer
Kevin Urquhart

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

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron