Concatenating strings from multiple records into a single value

Say that you want to concatenate values from several records into a single string.  The classic example is a list of categories where you want to present it to the user as Category Name followed by a comma separated list of all products for that category.

There are multiple solutions for this problem, but this one seems to be the most straight forward:

   1: Select C.Name, 

   2:     stuff((select ', ' + P.Name 

   3:             from Product as P

   4:                 where P.CategoryId = C.Id

   5:             for xml path('')),1,2,'') as ProductList

   6:     from Category as C