Create Comma Separated List in SQL Server

Assume for a minute that you have a table with records that you want to quickly put into a comma separated file (fig. 1). You don’t want to create a Microsoft Integrated Service package like this. Rather you just need something quick. There is a really cool way to do this directly in a select statement in SQL 2005 and greater. It is called STUFF (you can read about it here).

commaseparatedvalues(Figure 1)

[code language=”SQL”]
SELECT STUFF( (SELECT ‘, ‘ + job_code
FROM [tablename]
FOR XML PATH(”), TYPE).value(‘.’, ‘varchar(max)’)
,1, 2, ”)


This will produce one record that looks like this.

[code language=”text”]
0002, 0004, 0005, 0006, 0008, 0011, 0017, 0018, 0020, 0023, 0027

Hope this helps someone like it helped me.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , | Posted under ASP .NET 4.0, Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *