Concatenating values from multiple rows with T-SQL

Today I had the problem that I needed to concatenate values of a column from multiple rows of a table. I was developing a report for SQL Server Reporting Services and the data source was a SQL Server Database. So I ended up using T-SQL with the special FOR XML PATH – Command. The FOR XML command is used with T-SQL to format the results of a query as XML.

The following table contains a list of some car manufacturers:

image

To get a comma separated list of manufacturers I can take the following T-SQL statement:

   1:  SELECT STUFF( (SELECT ',' + Manufacturer From CarManufacturer FOR XML PATH('') ), 1, 1, '') as 'Car Manufacturers'

 

The result looks like this:

image