Thursday, 3 July 2008

SQL: Concatenating Rows into a Scalar Value

This is an issue which pops up from time to time, often when passing values back to .Net applications.

The Problem
Taking values in rows and creating a single string value containing a delimited list of all those values.

The Old Solution
The method I have used in the past is to use the FOR XML clause and build a string that way. It works.

DECLARE @DepartmentName VARCHAR(1000)
SELECT @DepartmentName = Name + ','
FROM AdventureWorks.HumanResources.Department
WHERE GroupName 'Executive General and Administration'
FOR XML PATH ('')
SELECT @DepartmentName AS DepartmentNames


The Sexier Solution
This idea comes courtesy of Ken Simmons at SQL Tips... so simple and I'm kind of annoyed that I've never tried it myself!

DECLARE @DepartmentName VARCHAR(1000)
SELECT @DepartmentName COALESCE(@DepartmentName,'') + Name ';' 
FROM AdventureWorks.HumanResources.Department
WHERE GroupName 'Executive General and Administration'
SELECT @DepartmentName AS DepartmentNames


What Were the Actual Results from Testing?
Sadly, the sexier solution under-performs in comparison to the FOR XML solution.

MethodMillisecondsBytesReceivedRows Returned
FOR XML7898913
COALESCE2651050560921


Obviously this lag is due to the number of rows which are returned to the client. Based on the stats, the FOR XML does all it's aggregation and concatenation server-side whereas the COALESCE method does it client-side.

So I guess that means the old way is still the best. Or is it? I wonder in what other circumstances COALESCE could be used? Ok so it under-performed in this instance but I suspect there are other uses which could be of great value. Got any ideas?