SQL Server: converting rows to lists with & without Coalesce

Today I had a data layout issue to solve for a Reporting Services report I was writing.

The report had to aggregate data from another table, but lay out the values into one row-space, comma seperated. These sorts of problems frequently crop up in less simple reports.

I’m going to use the MSDN pubs database as an example. If you don’t have it, you can install it from the MSDN site.

Consider this query:

SELECT title FROM titles WHERE type = 'psychology'

This will return 5 rows:

Computer Phobic AND Non-Phobic Individuals: Behavior Variations
Is Anger the Enemy?
Life Without Fear
Prolonged Data Deprivation: Four Case Studies
Emotional Security: A New Algorithm

You can use the COALESCE keyword to aggregate these values together into a comma-seperated string, but this requires the use of a SQL Parameter. Not a problem in itself, but you might be in a place where you can’t use parameters:

DECLARE @Titles VARCHAR(250)
SELECT @Titles = COALESCE(@Titles,'') + title + ',' FROM titles
WHERE type = 'psychology'
SELECT @Titles

If you’re using this in a SQL Statement, maybe as a subselect, then this won’t work, as you can’t use the parameters within the SELECT, without turning it into a Stored Procedure, which seems overkill when all you wanted was a SELECT statement. This approach, however, will work:

SELECT STUFF(
(SELECT ', ' + title  FROM titles
WHERE type = 'psychology' FOR XML PATH('')
),1,2,'')

Both of these examples will output:

Computer Phobic AND Non-Phobic Individuals: Behavior Variations, Is Anger the Enemy?, Life Without Fear, Prolonged Data Deprivation: Four Case Studies, Emotional Security: A New Algorithm

In the last example, the outer SELECT can be part of a larger statement.  In fact, you could do away with the STUFF, as it’s the FOR XML PATH('') that does the magic. The STUFF just gets rid of the first comma.