Once in awhile I run across a need to create a delimited list from a set of rows in a SQL table.  The old-school method is to use a cursor to iterate over these rows and build a dynamic string.  As we all know, cursors are expensive and should be avoided whenever possible.  I recently found a better way to accomplish this and wanted to share it in case you’ve run into this before.

For this example, I’m going to be working with a simple Cities table that contains a City field and a State field.  A simple SELECT statement yields the following result:

select * from Cities where State = 'New York'

The first step is to transform this result into a chunk of XML, and for that the FOR XML statement is perfect.  Consider the following SQL:

declare @xml varchar(1000)
set @xml = (select City from Cities where State = 'New York' for xml path(''))

This returns the following (I trimmed it down a little to fit on the page, but it does return all rows):

<City>New York City</City><City>Buffalo</City><City>Rochester</City>

Now that we have an XML string, we can just use the built-in REPLACE functions to remove the XML nodes:

set @xml = replace(@xml, '<City>', ',')
set @xml = replace(@xml, '</City>','')

This returns the following:

,New York City,Buffalo,Rochester,Yonkers,Syracuse

Now, the only thing left is to remove the beginning space and comma from this for which the new SQL 2008 STUFF function is perfect:

select stuff(@xml,1,1,'')

This returns the following, which is exactly what we need:

New York City,Buffalo,Rochester,Yonkers,Syracuse

Now, chances are you don’t want to have to declare variables and do all this in multiple operations.  This can all be wrapped into a single select statement, which makes it easy to implement:

select stuff((replace(replace(
       (select City from Cities where State = 'New York' for xml path('')), '<City>', ','
       ), '</City>','')),1,1,'')

That’s it!  Pretty slick, huh?