将多行转换为一行，并用逗号作为分隔符

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

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;

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.

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

 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

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

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

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;

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.

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