Convert multiple rows into one with comma as separator

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?

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

Answer 2

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;

Answer 3

good review of several approaches:

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

Answer 4

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

Answer 5

 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