将查询结果连接到以逗号分隔的单行值[duplicate]

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

可能重复：SQL Server：我可以逗号将多行划分为一列吗？

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?

如何使用T-SQL获得此功能？ UNPIVOT？

3 个解决方案

#1

You can use the COALESCE function to achieve this:

您可以使用COALESCE功能来实现此目的：

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.

这将在sql server 2000及更高版本（也可能是早期版本）中运行。请注意，您在sql server 2000中没有varchar（max）。

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

在更高版本的sql server（2005及更高版本）中，也可以使用XML Path（）执行此操作

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

#2

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

你可以这样做，除了它显示到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('')"

#3

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

#1