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 fairly 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 to 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 the 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, providing 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.