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.
Returns a string which contains a delimited list of IDs, starting with the top/root of a hierarchy and ending with the specified ID.
PATH ( <ID_ColumnName>, <Parent_ColumnName> )