Jane Dallaway

Jane Dallaway

Jane Dallaway  //  Service Delivery manager, photographer, dog owner, gardener, reader, learner, software developer and occasional snowboarder

This blog contains all sorts of bits and bobs, from development related stuff, through process and productivity stuff, to photography stuff, and general inspiration things. It's a bit all over the place with no real theme, but then so am I

Email: jane @ dallaway.com
Also at:    

 
Apr 28 2010

TSQL: Get a list of values into a comma separated list without using a UDF

I needed to create a comma separated list from a series of values in a column in the database the other day, and being mindful of the affect of scalar UDFs on the query plan (after attending SQLBits VI) I looked around for an alternative method to my previous favourite using COALESCE.  A couple of blog comments mentioned using STUFF and FOR XML PATH to do this, and after a bit more research I found some examples from Kodyaz Development Resources which seemed to do the trick 

SELECT 
  STUFF(
    (
      SELECT ',' + <ColumnName>
      FROM <TableName>
      FOR XML PATH('')
    ), 1, 1, '') as CommaSeparatedList

1 comment

May 17, 2010
Paul said...
nice - very hand little script :)

Leave a comment...