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

group_concat(field_here, ',') if you're using MySQL. array_to_string(array_accum(field_here), ',') if you're using PostgreSQL. but since you're using MSSQL, just create a function that returns the comma-delimited string. Use ConcernedOfTunbridgeW code snippet, put in a function — Hao, Jun 26 '09 at 09:45
@van: The way the question is posed looks to me like at least he made an effort in trying to be as clear as possible and maintain readability, therefore, I assume he also took the trouble trying to find an answer on his own. — Lieven Keersmaekers, Jun 26 '09 at 11:33
possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Factor Mystic, May 23 '14 at 18:50
@van that's pretty funny, I thought your comment was recent and then I noticed it is over 5 years old; this question [is *still* asked at least once a week](http://stackoverflow.com/q/26495974/61305). — Aaron Bertrand, Oct 21 '14 at 21:17

score 72 · Answer 1 · answered Jun 26 '09 at 09:50

72

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

answered Jun 26 '09 at 09:50

Lieven Keersmaekers

57,207
13
112
146

4

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? – John Saunders Jun 26 '09 at 09:56
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. – Lieven Keersmaekers Jun 26 '09 at 10:06
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) – NateJ Mar 10 '16 at 20:59
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. – Lieven Keersmaekers Mar 11 '16 at 06:21

score 56 · Accepted Answer · edited Jul 26 '16 at 23:31

56

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

edited Jul 26 '16 at 23:31

Slight

1,541
3
19
38

answered Jun 26 '09 at 09:37

ConcernedOfTunbridgeWells

64,444
15
143
197

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? – Factor Mystic Aug 14 '14 at 14:15
1

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. – Slight Jul 26 '16 at 22:10
@ConcernedOfTunbridgeWells - Can this approach be used to perform dynamic SQL statements, such as: `SELECT col1 as 'col1 name', coln as 'coln name' FROM ` ?
– NickBraunagel Dec 18 '17 at 22:18
@NickBraunagel yes, you can do that and I've done it before. – ConcernedOfTunbridgeWells Dec 19 '17 at 15:07
Thanks, I ended up adding brackets (`[`) around the display name to achieve this. – NickBraunagel Dec 19 '17 at 15:25

score 35 · Answer 3 · edited Aug 31 '17 at 11:34

35

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

edited Aug 31 '17 at 11:34

John

6,693
3
51
90

answered Jan 25 '17 at 12:33

gpanagakis

519
5
7

2

It's about time this function was created! – Davos Nov 14 '17 at 08:05
An equivalent to LISTAGG() was a long-time coming, and should definitely be used in SQL Server 2017. – Paul Apr 25 '18 at 15:45
This should be upvoted! – Prabhat G Nov 01 '21 at 05:42

score 29 · Answer 4 · answered Nov 18 '14 at 05:38

29

You can do this using stuff:

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

answered Nov 18 '14 at 05:38

Vaibhav D

605
9
12

Basically just a simplified version of the answer here -- http://stackoverflow.com/questions/6899/how-to-create-a-sql-server-function-to-join-multiple-rows-from-a-subquery-into -- 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 Mar 10 '16 at 20:58

score 10 · Answer 5 · answered Jun 26 '09 at 09:43

10

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.

answered Jun 26 '09 at 09:43

John Saunders

160,644
26
247
397

If "CarName" is an int (ie, "CarId), do SELECT @CarList+=CONVERT(varchar(20),CarId,0)+N',' – samus Feb 20 '14 at 14:26

Emre Guldogan · Answer 6 · 2016-01-11T08:26:00.817

10

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)

edited Jan 11 '16 at 08:26

answered Aug 01 '14 at 08:57

Emre Guldogan

590
9
19

Is there a solution for this in Netezza – Shabar Mar 14 '15 at 08:19
Nice one for adding the where and order clause – Nebulosar Oct 17 '18 at 12:05

score 8 · Answer 7 · edited Sep 13 '17 at 09:37

8

Please try this with the following code:

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

edited Sep 13 '17 at 09:37

Martin Liversage

104,481
22
209
256

answered Sep 13 '17 at 09:29

Rahul Srivastava

373
5
8

score 5 · Answer 8 · answered Apr 25 '16 at 12:33

5

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

answered Apr 25 '16 at 12:33

User

804
8
22

@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 – User Apr 25 '16 at 12:56
@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... – PhilS Apr 25 '16 at 13:37

Concatenating Column Values into a Comma-Separated List

8 Answers8

Linked

Related