Concatenating Rows in Sql Query

Programming Tips-And-Tricks

Sometimes it is required e.g, to get a comma-delimited-values list in a field from a relational database table. This is only possible to find some way to concatenate rows of the table. Following is one of the possible solutions using ‘FOR XML PATH’ expression in T-Sql. You may find other solutions as well.

Suppose you have a table ‘animals’ with the columns id and name. You want to get the list of names from this table. Here’s the simple query.

  select 
   replace(
     replace(
       replace(
         (select name from animals order by id for xml path('')),
         '</name><name>',
         ', ' -- delimiter
       ),
       '</name>',
       ''
     ),
     '<name>',
     ''
   )

Leave a Reply

Your email address will not be published. Required fields are marked *