9

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?

Community
  • 1
  • 1
abatishchev
  • 98,240
  • 88
  • 296
  • 433

3 Answers3

20

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('')
David Hall
  • 32,624
  • 10
  • 90
  • 127
  • @David Hall: I like the first one. Second unfortunately adds a comma to the end of each record, for the last one too. – abatishchev Mar 02 '10 at 22:25
  • 1
    @abatishchev glad you found this helpful. Do have a look at this post http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx you may find that for large result sets the xml path method is better. – David Hall Mar 02 '10 at 22:29
  • This is easy when you have only certain amount of data. However, if you want only part of the data, say only "user1 and user2" - this will fail with error: **Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.** – Shai Alon Jan 29 '17 at 12:39
  • "This will work in sql server 2000 and later .... Note that you don't have varchar(max) in sql server 2000 though.". So it won't work! Change MAX to 8000 – Fandango68 Aug 17 '17 at 05:50
1

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('')"
djangofan
  • 28,471
  • 61
  • 196
  • 289
0
declare @result varchar(max)
set @result = ''
select @result = @result + name + ',' from users 
if @result <> '' set @result = left(@result,len(@result)-1)
print @result
  • @DyingCactus: How to call this in cycle of recursively so resulting string like 'user1, user2, user3, ... userN' could come out – abatishchev Mar 02 '10 at 22:13
  • Do you mean how to get the string without comma at end in one statement? David Hall's answer will do that. –  Mar 02 '10 at 22:40