Join query result to a single line of values separated by comma
sql, sql-server, t-sql
Solution
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('')
Problem
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`?