将 sql 值连接到变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4792098/
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
Concatenate sql values to a variable
提问by chris
On a SQL Server 2008 I'm trying to get a comma separated list of all selected values into a variable.
在 SQL Server 2008 上，我试图将所有选定值的逗号分隔列表放入一个变量中。
SELECT field
FROM table
returns:
返回：
+-------+
| field |
+-------+
| foo |
+-------+
| bar |
+-------+
I'd like to get: "foo, bar, "
我想得到：“foo，bar，”
I tried:
我试过：
DECLARE @foo NVARCHAR(MAX)
SET @foo = ''
SELECT @foo = @foo + field + ','
FROM TABLE
PRINT @foo
Which returns nothing. What am I doing wrong?
什么都不返回。我究竟做错了什么？
回答by gbn
You'll need to change NULLs
您需要更改 NULL
SELECT @foo = @foo + ISNULL(field + ',', '')
FROM TABLE
or remove them
或删除它们
SELECT @foo = @foo + field + ','
FROM TABLE
WHERE field IS NOT NULL
回答by RichardTheKiwi
That happens if you have even a SINGLE field in the table that is NULL. In SQL Server, NULL + <any> = NULL. Either omit them
如果您在表中甚至有一个为 NULL 的 SINGLE 字段，就会发生这种情况。在 SQL Server 中，NULL + <any> = NULL. 要么省略它们
SELECT @foo = @foo + field + ','
FROM TABLE
WHERE field is not null
Or work around them
或者解决它们
SELECT @foo = @foo + isnull(field + ',', '')
FROM TABLE
You can write the whole thing without the leading SET statement which is more common. This query below returns "foo,bar" with no trailing comma
您可以在没有更常见的前导 SET 语句的情况下编写整个内容。下面的这个查询返回没有尾随逗号的“foo,bar”
DECLARE @foo NVARCHAR(MAX)
SELECT @foo = isnull(@foo + ',', '') + field
FROM TABLE
WHERE field is not null
PRINT @foo
回答by guest
Don't forget to use LTRIMand RTRIMaround @foo(when data type is char/varchar) in the concatenation other it will not give expected results in SQL 2008 R2.
不要忘记在连接中使用LTRIMand RTRIMaround @foo（当数据类型为char/ 时varchar），否则它不会在 SQL 2008 R2 中给出预期的结果。
回答by Tidorith
As per the comment Lukasz Szozda made on one of the answers here, you should not use your indicated method to aggregate string values in SQL Server, as this is not supported functionality. While this tends to work when no order clause is used (and even if no exception to this tendency has ever been documented), Microsoft does not guarantee that this will work, and there's always a chance it could stop working in the future. SQL is a declarative language; you cannot assume that behaviour that is not explicitly defined as being the correct behaviour for interpreting a given statement will continue working.
根据 Lukasz Szozda 对此处答案之一的评论，您不应使用您指定的方法在 SQL Server 中聚合字符串值，因为这是不支持的功能。虽然这在不使用 order 子句时往往会起作用（即使没有记录过这种趋势的例外情况），但 Microsoft 不保证这会起作用，并且将来总是有可能停止工作。SQL 是一种声明性语言；您不能假设未明确定义为解释给定语句的正确行为的行为将继续有效。
Instead, see the examples below, or see this page for a review of valid ways to achieve the same result, and their respective performance: Optimal way to concatenate/aggregate strings
相反，请参阅下面的示例，或查看此页面以了解实现相同结果的有效方法及其各自的性能：连接/聚合字符串的最佳方法
Doing it in a valid way, whichever way you end up using, still has the same considerations as in the other answers here. You either need to exclude NULL values from your result set or be explicit about how you want them to be added to the resulting string.
以有效的方式进行操作，无论您最终使用哪种方式，仍然具有与此处其他答案相同的注意事项。您要么需要从结果集中排除 NULL 值，要么明确说明您希望如何将它们添加到结果字符串中。
Further, you should probably use some kind of explicit ordering so that this code is deterministic - it can cause all sorts of problems down the line if code like this can produce a different result when running on the same data, which may happen without an explicit ordering specified.
此外，您可能应该使用某种显式排序，以便此代码具有确定性 - 如果这样的代码在运行相同数据时可能产生不同的结果，则可能会导致各种问题，而这种情况可能会在没有显式指定的订购。
--Null values treated as empty strings
SET @Foo =
STUFF /*Stuff is used to remove the seperator from the start of the string*/
( (SELECT N','/*separator*/ + ISNULL(RTRIM(t.Field), '' /*Use an emptry string in the place of NULL values*/) /*Thing to List*/
FROM TABLE t
ORDER BY t.SomeUniqueColumn ASC /*Make the query deterministic*/
FOR XML PATH, TYPE).value(N'.[1]',N'varchar(max)')
,1
,1 /*Length of separator*/
,N'');
--Null values excluded from result
SET @Foo =
STUFF /*Stuff is used to remove the seperator from the start of the string*/
( (SELECT N','/*separator*/ + RTRIM(t.Field) /*Thing to List*/
FROM TABLE t
WHERE t.Field IS NOT NULL
ORDER BY t.SomeUniqueColumn ASC /*Make the query deterministic*/
FOR XML PATH, TYPE).value(N'.[1]',N'varchar(max)')
,1
,1 /*Length of separator*/
,N'');
