January 30, 2009

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.

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.


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.

tags: Tutorials, SQL
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.