Creating Comma Separated List in SQL 

It seems that every client project I have started in recent months has involved a new form of complexity in SQL Server scripts.  The most recent task that I had to tackle was with generating a comma separated list based on entries in a table.  Now this was something that I needed for each and every row of a result set, so I didn't want to do the actual list creation in .NET.  So, reaching back to some content and examples that I built for my "Creating Dynamic SQL Pivots" article which will be published in an upcoming edition of SQL Server I found the following helpful snippet of code.

DECLARE @List VARCHAR(5000)
SELECT @List = COALESCE(@List + ', ' + Display, Display)
FROM TestTable
Order By Display

This query although farily simple is a bit hard for some to comprehend due to the handling of a statement where the parameter is both on the left and right sides of the assignment operator.  What I do with this script is first declare a variable to hold the list string, at this time the value for the string is null, this is a VERY important point.  Then in the select I set the value of the list equal to the results of the COALESCE call.

First, a bit of background on the COALESCE function for those that are not aware of what its purpose is.  This function will return the first non-null result from the list of input parameters.  In this case we pass it two options.

1. @List + ', ' + display
2. Display

This is where the real key in knowing the way that String Variables work in SQL.  SInce when the select first executes the value for @List is null, any concatenations to it are also null, making the first option not valid.  This then sets the value of @List = the value from display.  Allowing future iterations to append the comma between the existing values and the new item to add.

Implementation Options

The example provided above is really a "simple" example of this implementation in the form of a simple SELECT statement.  To make this a helpful piece of SQL code for your solutions you might want to wrap this into a Stored Procedure or potentially a User Defined Function if it is needed on a row by row basis.  For my specific example I created a Function that I could call to get the needed list information.

Summary

This article was intended to be a quick overview of one method to create a comma separated list within SQL.  There are other options available in SQL Server, the reason I like this solution is that it will work on SQL Server 2000 and newer, proviging a wide variety of support options.

As always, feel free to share feedback below, if you have specific technical questions please feel free to post to the forums here.

Share/Save This Article:      

Comments

Name (required)

Email (required)

Website

Enter the code shown above:

Notify me of followup comments via e-mail