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:

Sample Report

To produce a list you can use the following SQL:

DECLARE @result varchar(max)
 
SELECT    @result = coalesce(@result + ', ', '') + Name
FROM    Person.StateProvince
WHERE    Person.StateProvince.CountryRegionCode = @CountryRegionCode
 
RETURN @Result

The way this works is the COALESCE function returns the first non-null expression. For the first record this would be the empty string + Name as @Result would be null. However, the interesting thing is that because we keep concatinating to @Result a single value is returned from the whole result set. The only issue is that for this to work we need a variable to keep storing the information in. So if you want to apply this against another table that supplies the filter, you'll have to create a function that returns the string. As this is a scalar function (ie doesn't return a result set) its use within another query is extremely easy. Assuming a function for the above is called dbo.fnRegionsToString we are left with:

SELECT Name AS Country, dbo.fnRegionsToString(CountryRegionCode) States
FROM
         Person.CountryRegion

The whole script which you can test against the AdventureWorks database is:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID(N'[dbo].[fnRegionsToString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION dbo.fnRegionsToString
GO
 
CREATE FUNCTION dbo.fnRegionsToString
(
    @CountryRegionCode varchar(5)
)
RETURNS varchar(Max)
AS
BEGIN
 
    DECLARE @result varchar(max)
 
    SELECT    @result = coalesce(@result + ', ', '') + Name
    FROM    Person.StateProvince
    WHERE    Person.StateProvince.CountryRegionCode = @CountryRegionCode
    
    RETURN @Result
END
GO
 
SELECT Name AS Country, dbo.fnRegionsToString(CountryRegionCode) States
FROM         Person.CountryRegion

This makes for a very clean solution to an otherwise painful problem. So the next time you need to turn a lot of records into a single one, check to see if this technique can help.

kick it on DotNetKicks.com
Digg It!DZone It!StumbleUponTechnoratiRedditDel.icio.usNewsVineFurlBlinkList

Currently rated 3.1 by 22 people

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

Comments