Concatenating Column Values into a Comma-Separated List

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

Solution 1:

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

Solution 2:

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 = 
    case when @aa = ''
    then CarName
    else @aa + coalesce(',' + CarName, '')
    end
  from Cars

print @aa