SQL 查询 - 将结果连接成一个字符串 [英] SQL Query - Concatenating Results into One String

问题描述

我有一个包含此代码的 sql 函数:

I have a sql function that includes this code:

DECLARE @CodeNameString varchar(100)

SELECT CodeName FROM AccountCodes ORDER BY Sort

我需要将选择查询的所有结果连接到 CodeNameString 中.

I need to concatenate all results from the select query into CodeNameString.

显然，C# 代码中的 FOREACH 循环可以做到这一点，但我如何在 SQL 中做到这一点?

Obviously a FOREACH loop in C# code would do this, but how do I do it in SQL?

推荐答案

如果您使用的是 SQL Server 2005 或更高版本，则可以使用此 FOR XML PATH &东西技巧:

If you're on SQL Server 2005 or up, you can use this FOR XML PATH & STUFF trick:

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 在第一个字符处放置一个无"字符，例如消除多余"的第一个逗号，为您提供您可能正在寻找的结果.

The FOR XML PATH('') basically concatenates your strings together into one, long XML result (something like ,code1,code2,code3 etc.) and the STUFF puts 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.

更新: 好的 - 我理解这些评论 - 如果您在数据库表中的文本已经包含像 <、> 或&，那么我目前的解决方案实际上会将它们编码为<, >, 和 &.

UPDATE: OK - I understand the comments - if your text in the database table already contains characters like <, > or &, then my current solution will in fact encode those into <, >, and &.

如果您对该 XML 编码有疑问 - 那么是的，您必须查看@KM 提出的解决方案，该解决方案也适用于这些字符.来自我的一个警告:这种方法更多资源和处理密集型 - 所以你知道.

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 warning from me: this approach is a lot more resource and processing intensive - just so you know.

这篇关于SQL 查询 - 将结果连接成一个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！