Friday, June 17, 2011

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

No comments:

Post a Comment