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.

Method	Milliseconds	BytesReceived	Rows Returned
FOR XML	78	9891	3
COALESCE	265	10505	60921

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?