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:

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.