Convert multiple rows into one with comma as separ

问题:

This question already has an answer here:

• How to concatenate text from multiple rows into a single text string in SQL server? 44 answers

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?

回答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))

回答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

回答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.

回答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

回答5:

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

回答6:

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

回答7:

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#.

回答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;

回答9:

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.

回答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;