Convert multiple rows into one with comma as separator [duplicate]

Question

If I issue SELECT username FROM Users I get this result:

username
--------
Paul
John
Mary

but what I really need is one row with all the values separated by comma, like this:

Paul, John, Mary

How do I do this?

Dey

Answer 1

This should work for you. Tested all the way back to SQL 2000.

create table #user (username varchar(25))

insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')

declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user

select SUBSTRING(@tmp, 0, LEN(@tmp))

Cotangent

+1, but select SUBSTRING(@tmp, 0, LEN(@tmp)) looks incorrect (to me) while apparently working (I tried it). The...turgid...prose of the MSDN page on substring fails to clarify why it works, but I guess the end point is start_expression + length_expression without correcting start_expression, and since if you start with a number less than 1 it starts with "the first character" (e.g., 1), I guess it sort of works by the back door. I think I'll use select SUBSTRING(@tmp, 1, LEN(@tmp) - 1) instead, though. – Cystoscope

Yeah, apparently, since select SUBSTRING('testing', -2, 5) gives us 'te' (e.g., exactly what select SUBSTRING('testing', 1, 2) would give us), as in both cases the resulting (exclusive) end index is 3. Not behavior I'd want to rely on. Is there some specific reason you do? – Cystoscope

No special reason, just the fact that my roots are in C++ so I'm used to zero-offset arithmetic... – Cotangent

@T.J.Crowder - In fact because the goal is to remove the last character I believe stuff(str,len(str)-1,1,'') is faster. – Phanotron

@Cotangent - I seems that select @tmp = @tmp + username + ', ' from #user does a row by row iteration. I was expecting that to cause an error instead. How does it really work ? – Idelson

@BoratSagdiyev -- You are correct, that's exactly what it's doing. That syntax expands into what is effectively a mini-cursor. In general, the FOR XML PATH solution Hogan provides below is faster for large cases and if you don't care about XML escaping of your special characters. – Cotangent

@Cotangent - I used your code in one of my answers. One of the other answerers said this about your code - The fact that the code you used worked is an unwanted side-effect of something else and is not supported by MS, meaning that it might stop working in some future version of SQL Server. Also, the order is not guaranteed. #23159679 – Idelson

Was really missing my GROUP_CONCAT() function. This rocks man! – Microdot

SQL server 2017/ SQL Azure supports STRING_AGG for the purpose - learn.microsoft.com/en-us/sql/t-sql/functions/… https://mcmap.net/q/40515/-how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-server – Ogdon

Why does it not work when you add a complex ORDER BY? – Albumose

Answer 2

 select
   distinct  
    stuff((
        select ',' + u.username
        from users u
        where u.username = username
        order by u.username
        for xml path('')
    ),1,1,'') as userlist
from users
group by username

had a typo before, the above works

Phanotron

You are a genius. Also, this puts one space in front of the list. Make the STUFF options 1, 2 and it'll remove that space. I need to figure out how the hell this works, now. – Fineman

@Fineman - Thanks, if you have a space then you must have had ', ' and not ',' as the first item in the select. Key to understanding how it works, understand how SQL Server converts between XML and relational data. Good luck. – Phanotron

Ahh yeah you're exactly right. This is my first time using the STUFF function and didn't realize what it was actually doing! Thanks! – Fineman

Best option when having to include it in a large sql, thanks! – Washrag

Disclaimer: this will not work for data that has special characters such as < or & in it. – Allmon

@kehrk - is there a fix for that ? – Idelson

@BoratSagdiyev - kehrk is wrong, FOR XML auto encodes, see sqlfiddle.com/#!6/b824a/1 – Phanotron

@kehrk - ummm... no. Works fine. sqlfiddle.com/#!6/b824a/1 – Phanotron

This is great, especially because you can use in line with an existing TSQL statement and make this just be another column in the statement. Also allows the WHERE statement to be feed a value from the TSQL statement itself. Many thanks. – Cardiograph

Should be the accepted answer – Scent

For my use case, this performs much better if the where clause is duplicated so that it's also included in the outer select (otherwise it evaluates for every row in the table) – Differentia

distict is not working perfectly, can see the duplicate data in result. sqlfiddle.com/#!18/8c053b/1 – Flattie

@RamSingh - sqlfiddle.com/#!18/8c053b/5/0 – Phanotron

@RamSingh - also there was a prior comment that this will not work well for data that has character considered "special" by xml. – Phanotron

@Hogan, yes, if you remove the special characters from the data, even though it is not removing duplicated data. – Flattie

Answer 3

good review of several approaches:

http://blogs.msmvps.com/robfarley/2007/04/07/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql/

Article copy -

Coalesce is not the answer to string concatentation in T-SQL I've seen many posts over the years about using the COALESCE function to get string concatenation working in T-SQL. This is one of the examples here (borrowed from Readifarian Marc Ridey).

DECLARE @categories varchar(200)
SET @categories = NULL

SELECT @categories = COALESCE(@categories + ',','') + Name
FROM Production.ProductCategory

SELECT @categories

This query can be quite effective, but care needs to be taken, and the use of COALESCE should be properly understood. COALESCE is the version of ISNULL which can take more than two parameters. It returns the first thing in the list of parameters which is not null. So really it has nothing to do with concatenation, and the following piece of code is exactly the same - without using COALESCE:

DECLARE @categories varchar(200)
SET @categories = ''

SELECT @categories = @categories + ',' + Name
FROM Production.ProductCategory

SELECT @categories

But the unordered nature of databases makes this unreliable. The whole reason why T-SQL doesn't (yet) have a concatenate function is that this is an aggregate for which the order of elements is important. Using this variable-assignment method of string concatenation, you may actually find that the answer that gets returned doesn't have all the values in it, particularly if you want the substrings put in a particular order. Consider the following, which on my machine only returns ',Accessories', when I wanted it to return ',Bikes,Clothing,Components,Accessories':

DECLARE @categories varchar(200)
SET @categories = NULL

SELECT @categories = COALESCE(@categories + ',','') + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)

SELECT @categories

Far better is to use a method which does take order into consideration, and which has been included in SQL2005 specifically for the purpose of string concatenation - FOR XML PATH('')

SELECT ',' + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)
FOR XML PATH('') 

In the post I made recently comparing GROUP BY and DISTINCT when using subqueries, I demonstrated the use of FOR XML PATH(''). Have a look at this and you'll see how it works in a subquery. The 'STUFF' function is only there to remove the leading comma.

USE tempdb;
GO
CREATE TABLE t1 (id INT, NAME VARCHAR(MAX));
INSERT t1 values (1,'Jamie');
INSERT t1 values (1,'Joe');
INSERT t1 values (1,'John');
INSERT t1 values (2,'Sai');
INSERT t1 values (2,'Sam');
GO

select
    id,
    stuff((
        select ',' + t.[name]
        from t1 t
        where t.id = t1.id
        order by t.[name]
        for xml path('')
    ),1,1,'') as name_csv
from t1
group by id
; 

FOR XML PATH is one of the only situations in which you can use ORDER BY in a subquery. The other is TOP. And when you use an unnamed column and FOR XML PATH(''), you will get a straight concatenation, with no XML tags. This does mean that the strings will be HTML Encoded, so if you're concatenating strings which may have the < character (etc), then you should maybe fix that up afterwards, but either way, this is still the best way of concatenating strings in SQL Server 2005.

Apeldoorn

Alex, I added the whole article in case original link goes dead. I hope you will accept the changes. Thanks. Chenqui. – Idelson

@AlexKuznetsov - FYI - This article is over 7 years old and contains information which has not been true since SQL 2008 came out. – Phanotron

@Phanotron the answer is very old as well, and it was written at the time when 2008 was not widely adopted at all. I see no value in keeping this answer up-to-date. – Apeldoorn

@AlexKuznetsov the last one worked me to use in an inline query. thanks for the details answer. – Merrow

+1 for your use of the FOR XML('') version, I was struggling to get aggregated rows partitioned by the id. Your example not only fixed it, but I understand how it works. – Seeseebeck

Anyway to add "and" to the last comma separated item? – Gimlet

Interesting! When I asked about delimiting, the DBA's kept telling me to "stuff it". Seems I originally misinterpreted them. I have to go apologize now... – Cormophyte

Answer 4

building on mwigdahls answer. if you also need to do grouping here is how to get it to look like

group, csv
'group1', 'paul, john'
'group2', 'mary'

    --drop table #user
create table #user (groupName varchar(25), username varchar(25))

insert into #user (groupname, username) values ('apostles', 'Paul')
insert into #user (groupname, username) values ('apostles', 'John')
insert into #user (groupname, username) values ('family','Mary')


select
    g1.groupname
    , stuff((
        select ', ' + g.username
        from #user g        
        where g.groupName = g1.groupname        
        order by g.username
        for xml path('')
    ),1,2,'') as name_csv
from #user g1
group by g1.groupname

Slaveholder

Answer 5

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

source: http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Tonometer

Answer 6

You can use this query to do the above task:

DECLARE @test NVARCHAR(max)  
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test
SELECT field2 = @test 

For detail and step by step explanation visit the following link http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html

Shaikh

Answer 7

you can use stuff() to convert rows as comma separated values

select
EmployeeID,
stuff((
  SELECT ',' + FPProjectMaster.GroupName 
      FROM     FPProjectInfo AS t INNER JOIN
              FPProjectMaster ON t.ProjectID = FPProjectMaster.ProjectID
      WHERE  (t.EmployeeID = FPProjectInfo.EmployeeID)
              And t.STatusID = 1
              ORDER BY t.ProjectID
       for xml path('')
       ),1,1,'') as name_csv
from FPProjectInfo
group by EmployeeID;

Thanks @AlexKuznetsov for the reference to get this answer.

Merrow

Answer 8

In SQLite this is simpler. I think there are similar implementations for MySQL, MSSql and Orable

CREATE TABLE Beatles (id integer, name string );
INSERT INTO Beatles VALUES (1, "Paul");
INSERT INTO Beatles VALUES (2, "John");
INSERT INTO Beatles VALUES (3, "Ringo");
INSERT INTO Beatles VALUES (4, "George");
SELECT GROUP_CONCAT(name, ',') FROM Beatles;

Sourdine

Question was tagged as tsql, ie MS SQL Server. GROUP_CONCAT doesn't exist in SQL Server. Since SQL Server 2017 there is a STRING_AGG function, however, which has similar functionality. – Clicker

Answer 9

A clean and flexible solution in MS SQL Server 2005/2008 is to create a CLR Agregate function.

You'll find quite a few articles (with code) on google.

It looks like this article walks you through the whole process using C#.

Bearnard

Answer 10

If you're executing this through PHP, what about this?

$hQuery = mysql_query("SELECT * FROM users");
while($hRow = mysql_fetch_array($hQuery)) {
    $hOut .= $hRow['username'] . ", ";
}
$hOut = substr($hOut, 0, strlen($hOut) - 1);
echo $hOut;

Ibson

Oh my bad, it seems your running this through the console. – Ibson

I need this done in sql, not in php or whatever (I'm using c# actually) – Dey

Yeah I noticed it wasn't PHP. – Ibson