Concatenating Column Values into a Comma-Separated List

Question

What is the TSQL syntax to format my output so that the column values appear as a string, seperated by commas.

Example, my table CARS has the following:

CarID CarName
1 Porsche
2 Mercedes
3 Ferrari

How do I get the car names as : 'Porsche, Mercedes, Ferrari'

Thanks, Murali

Answer 1

You can do a shortcut using coalesce to concatenate a series of strings from a record in a table, for example.

declare @aa varchar (200)
set @aa = ''

select @aa = 
    coalesce (case when @aa = ''
                   then CarName
                   else @aa + ',' + CarName
               end
              ,'')
  from Cars

print @aa

Answer 2

DECLARE @CarList nvarchar(max);
SET @CarList = N'';
SELECT @CarList+=CarName+N','
FROM dbo.CARS;
SELECT LEFT(@CarList,LEN(@CarList)-1);

Thanks are due to whoever on SO showed me the use of accumulating data during a query.

Answer 3

SELECT LEFT(Car, LEN(Car) - 1)
FROM (
    SELECT Car + ', '
    FROM Cars
    FOR XML PATH ('')
  ) c (Car)

Answer 4

DECLARE @SQL AS VARCHAR(500)

SELECT @SQL ISNULL(@SQL ' ')+' ' + ColumnName FROM TableName

SELECT @SQL