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

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

print @aa

Solution 2

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

Solution 3

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 

Solution 4

You can do this using stuff:

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

Solution 5

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.

View more solutions

Comments

[John Saunders]

Thanks. I had never seen this syntax for naming the columns of a subquery. I wonder if you can edit your answer to include that part of the TSQL syntax that permits this?

[Lieven Keersmaekers]

It's a construction I picked up right here at Stackoverflow. I don't know where or even if it is mentioned in any TSQL specification.

[samus]

If "CarName" is an int (ie, "CarId), do SELECT @CarList+=CONVERT(varchar(20),CarId,0)+N','

[Factor Mystic]

why init @aa to '' instead of just checking is null in the first when? also, why the coalesce? lastly, don't you want to wrap CarName with isnull(CarName, '') to be safe?

[SMPH]

Is there a solution for this in Netezza

[NateJ]

Basically just a simplified version of the answer here -- stackoverflow.com/questions/6899/… -- but I found this one's simplicity more portable because you can plug it into a "snippet manager" like SSMSBoost or Redgate SQLPrompt and have it saved in your own personal toolbox.

[NateJ]

I wonder if this one is any faster or slower than the "STUFF" answer below from Vaibhav... Both are great and snippet-y (re-usable)

[Lieven Keersmaekers]

I doubt any performance difference is noticable as the grunt of the work is the same for both solutions. But as always, try both in different scenarios and measure if you want to be sure.

[User]

@PhilS, No, It will give correct answer, if you run the above query on employee table on the names, the sample result would be Jhon,Thomas,PhilS

[PhilS]

@john: Oh, you are right indeed. Sorry, my fault. I would reversed the downvote, but the system does not let me unless the answer is edited...

[Slight]

Your coalesce is in the wrong place; it should wrap CarName like @aa + coalesce(',' + CarName, '') (check suggested edit) to turn CarName to a blanks tring in cases where its null. As you have it, @aa will be blanked out every time CarName is NULL.

[Davos]

It's about time this function was created!

[NickBraunagel]

@ConcernedOfTunbridgeWells - Can this approach be used to perform dynamic SQL statements, such as: SELECT col1 as 'col1 name', coln as 'coln name' FROM <table> ?

[ConcernedOfTunbridgeWells]

@NickBraunagel yes, you can do that and I've done it before.

[NickBraunagel]

Thanks, I ended up adding brackets ([) around the display name to achieve this.

[Paul]

An equivalent to LISTAGG() was a long-time coming, and should definitely be used in SQL Server 2017.

[Nebulosar]

Nice one for adding the where and order clause

[Prabhat G]

This should be upvoted!