如何在 SQL Server 2005 中将多行组合成逗号分隔的列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/180032/
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
How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
提问by Joshua Carmody
Right now, I have a SQL Query like this one:
现在，我有一个像这样的 SQL 查询：
SELECT X, Y FROM POINTS
It returns results like so:
它返回这样的结果：
X Y
----------
12 3
15 2
18 12
20 29
I'd like to return results all in one row, like this (suitable for using in an HTML <AREA> tag):
我想在一行中返回结果，就像这样（适合在 HTML <AREA> 标签中使用）：
XYLIST
----------
12,3,15,2,18,12,20,29
Is there a way to do this using just SQL?
有没有办法只使用 SQL 来做到这一点？
采纳答案by Ben Hoffstein
DECLARE @XYList varchar(MAX)
SET @XYList = ''
SELECT @XYList = @XYList + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y) + ','
FROM POINTS
-- Remove last comma
SELECT LEFT(@XYList, LEN(@XYList) - 1)
回答by Joshua Carmody
Thanks for the quick and helpful answers guys!
感谢您的快速和有用的答案！
I just found another fast way to do this too:
我也找到了另一种快速的方法来做到这一点：
SELECT STUFF(( SELECT ',' + X + ',' + Y
FROM Points
FOR
XML PATH('')
), 1, 1, '') AS XYList
Credit goes to this guy:
归功于这个人：
回答by Cade Roux
Using the COALESCEtrick, you don't have to worry about the trailing comma:
使用这个COALESCE技巧，您不必担心尾随逗号：
DECLARE @XYList AS varchar(MAX) -- Leave as NULL
SELECT @XYList = COALESCE(@XYList + ',', '') + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y)
FROM POINTS
回答by Carter Medlin
Starting in SQL 2017, you can use STRING_AGG
从 SQL 2017 开始，您可以使用 STRING_AGG
SELECT STRING_AGG (X + ',' + Y, ',') AS XYLIST
FROM POINTS
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
回答by Peter
DECLARE @s VarChar(8000)
SET @s = ''
SELECT @s = @s + ',' + CAST(X AS VarChar) + ',' + CAST(Y AS VarChar)
FROM POINTS
SELECT @s
Just get rid of the leading comma
去掉前面的逗号
