You are here:
Microsoft SQL 2008 Database Server Microsoft SQL 2008 Database Server

SQL Concatenation - Get column values as comma seperated list using XML PATH() instead of UDF's using SQL COALESCE

I am currently working on a project that required me to produce a list of comma separated values, for different rows in a Microsoft SQL resultset. I know there are various techniques that use COALESCE, but I thought I would share another useful way of producing the same result, but using XML PATH().

An example is below:

SELECT STUFF( ( SELECT ',' + category_name FROM categories FOR XML PATH('') ), 1, 1, '' ) AS categories_list

Nice and simple and very flexible!