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:

  • Would a query run in a try statement that failed if it was before the error occured?
  • Entity Framework long running query
  • Calling stored procedure with return value
  • Distinct query not working fine in sqlserver 2005
  • How to display image from sql server in ms access
  • Replace REGEXP_SUBSTR in SQL Server
  • CarID    CarName  
        1    Porsche  
        2    Mercedes  
        3    Ferrari  

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

  • How to setup Microsoft Word to fill in certain fields with data from a SQL Server database using a web service?
  • How do I add a total as the last row in my sql?
  • TSQL XML Query Question
  • SSRS BIDS 2008 data labels placement expression
  • SQL Server 2008 CLR vs T-SQL: Is there an efficiency/speed difference?
  • Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE
  • 8 Solutions collect form web for “Concatenating Column Values into a Comma-Separated List”

    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, '')
      from Cars
    print @aa
    SELECT LEFT(Car, LEN(Car) - 1)
    FROM (
        SELECT Car + ', '
        FROM Cars
        FOR XML PATH ('')
      ) c (Car)

    You can do this using stuff:

    SELECT Stuff(
        SELECT ', ' + CARS.CarName
        FROM CARS
        FOR XML PATH('')
        ), 1, 2, '') AS CarNames
    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.

    Another solution within a query :

            (select (', "' + od.ProductName + '"')
            from OrderDetails od (nolock)
            where od.Order_Id = o.Id
            order by od.ProductName
            FOR XML PATH('')), 1, 2, ''
        ) ProductNames
    from Orders o (nolock)
    where o.Customer_Id = 525188
    order by o.Id desc

    (EDIT: thanks @user007 for the STUFF declaration)

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

    If you are running on SQL Server 2017 or Azure SQL Database you do something like this :

     SELECT STRING_AGG(CarName,',') as CarNames

    Please try this with the following code:

    SELECT @listStr = COALESCE(@listStr+',' , '') + CarName
    FROM Cars
    SELECT @listStr
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.