SQL查询 - 将结果连接成一个字符串

Question

我有一个包含以下代码的SQL函数：

DECLARE @CodeNameString varchar(100)

SELECT CodeName FROM AccountCodes ORDER BY Sort

我需要将选择查询的所有结果连接成CodeNameString。

显然，C#代码中的FOREACH循环可以实现此操作，但在SQL中该怎么做呢？

- Matthew Jones

---

SQL Server 的哪个版本？ - marc_s

可能是重复的问题：SQL Server：我可以将多行用逗号分隔成一列吗？ 和：https://dev59.com/s3I-5IYBdhLWcg3whImk - OMG Ponies

已经回答过很多次了...但是要注意，不是所有的FOR XML PATH连接实现都能正确处理XML特殊字符（<、&、>等），就像我之前回答的一个问题一样：https://dev59.com/Q2445IYBdhLWcg3wH2rH#5031297 - KM.

Answer 1

如果你正在使用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, '')

FOR XML PATH('')将你的字符串连接成一个长的XML结果（类似于,code1,code2,code3等），STUFF在第一个字符处放置一个“空”字符，例如清除“多余”的第一个逗号，以便获得您可能正在寻找的结果。 更新：好的-我理解评论了-如果您数据库表中的文本已经包含像<、>或&这样的字符，则我的当前解决方案实际上会将其编码为<、>和&。如果您对此XML编码有问题-那么是的，您必须查看@KM提出的适用于这些字符的解决方案。我想警告您：这种方法需要更多的资源和处理时间-只是让您知道。

- marc_s

---

这段代码将无法正确处理 XML 特殊字符（如 <、&、> 等），与我之前的答案不同，可以在这里查看：https://dev59.com/Q2445IYBdhLWcg3wH2rH#5031297 - KM.

@jnm2：同意 - 如果有人需要那些特殊字符，那么“KM”的解决方案是正确的选择。然而：它需要更多的处理能力，因此我建议仅在您确实需要在输出中使用这些少数特殊XML字符时才使用它。 - marc_s

@marc_s 对于大多数使用情况，你如何提前知道这些字符将来是否会出现？假设你不需要它们是危险的。 你能展示一下KM方法更加资源密集的文档吗？我不明白为什么会这样。 - jnm2

@jnm2：是啊，我希望微软能够倾听我们的需求，最终为我们提供一个内置（并且经过优化！）函数来处理这个问题...... - marc_s

@userSteve：如果您不关心排序顺序，那就不用加 - 我之所以将其添加在这里是因为 OP 在问题中也使用了 ORDER BY。 - marc_s

Answer 2

DECLARE @CodeNameString varchar(max)
SET @CodeNameString=''

SELECT @CodeNameString=@CodeNameString+CodeName FROM AccountCodes ORDER BY Sort
SELECT @CodeNameString

- Alex

---

简单而优雅的回答。使用这种方法有什么限制吗？（NULL值由James Wisemann对此答案的变体处理） - Pac0

如果您在整数列上使用它，它将尝试将它们相加并失败。请使用CAST(int_col_name as varchar(10))。 - Andrew

这个答案很有用，因为你可以通过连接任何字符串文本来进一步格式化它，例如 '(' + myCol + '_' + myOtherCol + ')'。 - Andrew

Answer 3

@AlexanderMP的回答是正确的，但你也可以考虑使用coalesce来处理null值：

declare @CodeNameString  nvarchar(max)
set @CodeNameString = null
SELECT @CodeNameString = Coalesce(@CodeNameString + ', ', '') + cast(CodeName as varchar) from AccountCodes  
select @CodeNameString

- James Wiseman

---

是的，我忘记了coalesce/isnull。但是检查CodeName而不是@CodeNameString会更有意义吗？这样可能会丢失数据。最好的方法是使用普通的Where CodeName is not null，而不是使用coaleste。 - Alex

这绝对是最好、最优雅的解决方案，非常感谢！ - Federico Caccia

Answer 4

对于SQL Server 2005及以上版本，请使用Coalesce处理null值，如果是数字类型的值，则使用Cast或Convert函数 -

declare @CodeNameString  nvarchar(max)
select  @CodeNameString = COALESCE(@CodeNameString + ',', '')  + Cast(CodeName as varchar) from AccountCodes  ORDER BY Sort
select  @CodeNameString

- Vishal

---

100有点小，你觉得呢;-) - James Wiseman

@James- 嗯...刚刚是从原帖的问题中复制过来的，现在已经更新了...谢谢...顺便说一句，你真的比我快。 - Vishal

Answer 5

从MSDN上得知，在SELECT语句中不要使用变量来拼接值（即计算聚合值）。这可能会导致意外的查询结果。原因是SELECT列表中的所有表达式（包括赋值）不能保证对每个输出行都只执行一次。

以上内容似乎表明，如上所述的拼接方式无效，因为赋值可能比选择返回的行数更多。

- peter

Answer 6

这里有另一个现实生活中的例子，至少在2008版本（及以后版本）中可以正常工作。

这是原始查询，它使用简单的max()函数来获取至少一个值：

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

改进版，主要改进是我们以逗号分隔的形式显示所有值：

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

请注意，我们已经解决了我能想到的所有可能的NULL情况问题，并且我们还修复了数字值（字段排序）导致的错误。

- Timo Riikonen