SQL 将查询结果连接到以逗号分隔的单行值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2367162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join query result to a single line of values separated by comma
提问by abatishchev
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?
如何使用T-SQL？UNPIVOT?
回答by David Hall
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('')
回答by djangofan
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\Tools\BINN\osql" -S . -E -Q "SELECT name + ', ' FROM sysdatabases order by name for XML PATH('')"
回答by djangofan
declare @result varchar(max)
set @result = ''
select @result = @result + name + ',' from users
if @result <> '' set @result = left(@result,len(@result)-1)
print @result
