Turn multiple rows into a single string in SQL Server

June 27, 2008 22:56 by garrymc

I recently had the task of creating a report where they wanted one of the columns to list out all the users associated with a given report line item in a single comma separated string. My initial thoughts were, that this would involve some type of cursor or at least a loop of some description, which I'd then have to attach to the main result set. However, it seems there's a very nice little trick you can use that will solve this issue without the use of cursors or loops!

With the use of the COALESCE keyword you can create a list in a single line of SQL (except for declarations). I'll use the AdventureWorks database to demonstrate the technique. Assume  you need to get a list of Countries and the regions they contain, in a report similar to the one below: More...

Digg It!DZone It!StumbleUponTechnoratiRedditDel.icio.usNewsVineFurlBlinkList

Currently rated 3.1 by 22 people

  • Currently 3.136364/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5