Join query result to a single line of values separated by comma

Question

Possible Duplicate:
SQL Server: Can I Comma Delimit Multiple Rows Into One Column?

I have a query like this:

SELECT name from users

and it's result is a number of records:

1 user1
2 user2
3 user3

I want to get all this records in a single line separated by comma:

user1, user2, user3

and an empty line if query result is empty.

How to get this using T-SQL? UNPIVOT?

Answer 1

declare @result varchar(max)
set @result = ''
select @result = @result + name + ',' from users 
if @result <> '' set @result = left(@result,len(@result)-1)
print @result

Answer 2

You can use the COALESCE function to achieve this:

declare @result varchar(max)

select @result = COALESCE(@result + ', ', '') + name
from users

select @result

This will work in sql server 2000 and later (probably earlier versions too). Note that you don't have varchar(max) in sql server 2000 though.

In later versions of sql server (2005 and later), it is also possible to do this using XML Path()

select name + ','
from users
for xml path('')

Answer 3

You can do it like this too except its not formatted all that nicely when displayed to the DOS shell:

echo Batch file SQL 2005
echo. 
"%PROGRAMFILES%\Microsoft SQL Server\90\Tools\BINN\osql" -S . -E -Q "SELECT name + ', ' FROM sysdatabases order by name for XML PATH('')"