SQL 查询 - 将结果连接成一个字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5196371/
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
SQL Query - Concatenating Results into One String
提问by Matthew Jones
I have a sql function that includes this code:
我有一个包含此代码的 sql 函数：
DECLARE @CodeNameString varchar(100)
SELECT CodeName FROM AccountCodes ORDER BY Sort
I need to concatenate all results from the select query into CodeNameString.
我需要将来自选择查询的所有结果连接到 CodeNameString。
Obviously a FOREACH loop in C# code would do this, but how do I do it in SQL?
显然 C# 代码中的 FOREACH 循环可以做到这一点，但我如何在 SQL 中做到这一点？
回答by marc_s
If you're on SQL Server 2005 or up, you can use this FOR XML PATH & STUFFtrick:
如果您使用的是 SQL Server 2005 或更高版本，则可以使用以下FOR XML PATH & STUFF技巧：
DECLARE @CodeNameString varchar(100)
SELECT
@CodeNameString = STUFF( (SELECT ',' + CodeName
FROM dbo.AccountCodes
ORDER BY Sort
FOR XML PATH('')),
1, 1, '')
The FOR XML PATH('')basically concatenates your strings together into one, long XML result (something like ,code1,code2,code3etc.) and the STUFFputs a "nothing" character at the first character, e.g. wipes out the "superfluous" first comma, to give you the result you're probably looking for.
该FOR XML PATH('')基本符连接你的字符串连成一片，长XML结果（像,code1,code2,code3等）和STUFF上置“无”字的第一个字符，如抹了“多余的”第一个逗号，给你，你很可能结果寻找。
UPDATE:OK - I understand the comments - if your text in the database table already contains characters like <, >or &, then my current solutionwill in fact encode those into <, >, and &.
更新：好的 - 我理解这些评论 - 如果您在数据库表中的文本已经包含像<,>或 之类的字符&，那么我当前的解决方案实际上会将它们编码为<, >, 和&。
If you have a problem with that XML encoding - then yes, you must look at the solution proposed by @KM which works for those characters, too. One word of warningfrom me: this approach is a lot moreresource and processing intensive - just so you know.
如果您对该 XML 编码有疑问 - 那么是的，您必须查看@KM 提出的解决方案，该解决方案也适用于这些字符。我的一个警告：这种方法需要更多的资源和处理密集型 - 所以你知道。
回答by AlexanderMP
DECLARE @CodeNameString varchar(max)
SET @CodeNameString=''
SELECT @CodeNameString=@CodeNameString+CodeName FROM AccountCodes ORDER BY Sort
SELECT @CodeNameString
回答by James Wiseman
@AlexanderMP's answer is correct, but you can also consider handling nulls with coalesce:
@AlexanderMP 的回答是正确的，但您也可以考虑使用以下方法处理空值coalesce：
declare @CodeNameString nvarchar(max)
set @CodeNameString = null
SELECT @CodeNameString = Coalesce(@CodeNameString + ', ', '') + cast(CodeName as varchar) from AccountCodes
select @CodeNameString
回答by Vishal
For SQL Server 2005 and above use Coalescefor nullsand I am using Cast or Convert if there are numeric values-
对于 SQL Server 2005 及更高版本，请使用Coalescefor nulls，如果有，我将使用Cast 或 Convertnumeric values-
declare @CodeNameString nvarchar(max)
select @CodeNameString = COALESCE(@CodeNameString + ',', '') + Cast(CodeName as varchar) from AccountCodes ORDER BY Sort
select @CodeNameString
回答by peter
from msdn Do not use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. This is because all expressions in the SELECT list (including assignments) are not guaranteed to be executed exactly once for each output row
来自 msdn 不要在 SELECT 语句中使用变量来连接值（即计算聚合值）。可能会出现意外的查询结果。这是因为 SELECT 列表中的所有表达式（包括赋值）不能保证对每个输出行只执行一次
The above seems to say that concatenation as done above is not valid as the assignment might be done more times than there are rows returned by the select
上面似乎说，上面完成的连接是无效的，因为赋值的次数可能比 select 返回的行数还多
回答by Timo Riikonen
Here is another real life example that works fine at least with 2008 release (and later).
这是另一个现实生活中的例子，至少在 2008 版（及更高版本）中运行良好。
This is the original query which uses simple max()to get at least one of the values:
这是使用 simplemax()获取至少一个值的原始查询：
SELECT option_name, Field_M3_name, max(Option_value) AS "Option value", max(Sorting) AS "Sorted"
FROM Value_list group by Option_name, Field_M3_name
ORDER BY option_name, Field_M3_name
Improved version, where the main improvement is that we show all values comma separated:
改进版本，主要改进是我们显示所有值逗号分隔：
SELECT from1.keys, from1.option_name, from1.Field_M3_name,
Stuff((SELECT DISTINCT ', ' + [Option_value] FROM Value_list from2
WHERE COALESCE(from2.Option_name,'') + '|' + COALESCE(from2.Field_M3_name,'') = from1.keys FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'') AS "Option values",
Stuff((SELECT DISTINCT ', ' + CAST([Sorting] AS VARCHAR) FROM Value_list from2
WHERE COALESCE(from2.Option_name,'') + '|' + COALESCE(from2.Field_M3_name,'') = from1.keys FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'') AS "Sorting"
FROM ((SELECT DISTINCT COALESCE(Option_name,'') + '|' + COALESCE(Field_M3_name,'') AS keys, Option_name, Field_M3_name FROM Value_list)
-- WHERE
) from1
ORDER BY keys
Note that we have solved all possible NULLcase issues that I can think of and also we fixed an error that we got for numeric values (field Sorting).
请注意，我们已经解决NULL了我能想到的所有可能的案例问题，并且我们还修复了数值（字段排序）的错误。
