I’ve just read this interesting article from Anith Sen that lists many different ways to concatenate several row values into a single column (i.e. one row for each category with a field containing a comma separated list of products for that category).

I admit I never thought to use the FOR XML PATH solution to get this kind of result. Not only this is a very elegant way to write the query (you formally are not using other recursive approaches based on CTE), but it seems to be also the better solution from a performance point of view. I didn’t compared it with the CLR based one, but the performance seems to be so good with the FOR XML PATH solution that I think I will use that by default unless performances are very very critical – I would consider the CLR based solution only if I already have a CLR assembly deployed, but I wouldn’t deploy an assembly just for this feature.

Originally appeared on: http://sqlblog.com/blogs/marco_russo/archive/2008/08/13/the-use-of-for-xml-path-for-string-concatenation.aspx