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)

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.

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

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 *