row concatenation in sql2005

just reading about a new “FOR XML PATH” operator in sql2005 that apparently gives us a better way to do row concatenation than what we’ve been doing. instead of something like:

DECLARE @categories varchar(200)
SET @categories = NULL

SELECT @categories = COALESCE(@categories + ',','') + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)

SELECT @categories

you write this (you’ll need to strip out leading ‘,’):

SELECT ',' + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)
FOR XML PATH('')

one author saw up to a 10x speed improvement, but this will depend on the specifics of your query, so double-check the execution times if you use it. articles are here:

http://www.sqlmag.com/Article/ArticleID/96784/sql_server_96784.html

http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx