CODEWITHSUNDEEP
sql-servert-sql
Murali B
Murali B

Reputation: 875

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

Upvotes: 76

Views: 188330

Answers (8)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

For SQL Server 2017 and newer versions see the answer by gpanagakis: https://stackoverflow.com/a/41851771/218408

For SQL Server 2016 and older versions:

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

Upvotes: 76

Rahul Srivastava
Rahul Srivastava

Reputation: 373

Please try this with the following code:

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + CarName
FROM Cars
SELECT @listStr

Upvotes: 8

gpanagakis
gpanagakis

Reputation: 649

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

 SELECT STRING_AGG(CarName,',') as CarNames
 FROM CARS

Upvotes: 47

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66612

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

Upvotes: 56

User
User

Reputation: 804

 DECLARE @SQL AS VARCHAR(8000)
SELECT @SQL = ISNULL(@SQL+',','') + ColumnName FROM TableName
SELECT @SQL

Upvotes: 5

Emre Guldogan
Emre Guldogan

Reputation: 600

Another solution within a query :

select 
    Id, 
    STUFF(
        (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)

Upvotes: 10

Vaibhav D
Vaibhav D

Reputation: 625

You can do this using stuff:

SELECT Stuff(
    (
    SELECT ', ' + CARS.CarName
    FROM CARS
    FOR XML PATH('')
    ), 1, 2, '') AS CarNames

Upvotes: 30

John Saunders
John Saunders

Reputation: 161773

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.

Upvotes: 10

Related Questions