SQL查询—将结果连接到一个字符串[duplicate]

This question already has an answer here:

这个问题已经有了答案:

How to concatenate text from multiple rows into a single text string in SQL server? 43 answers
如何将多个行中的文本连接到SQL server中的单个文本字符串中?43个答案

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.

我需要将select查询的所有结果连接到CodeNameString。

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

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

6 个解决方案

#1

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

如果您使用的是SQL Server 2005或以上版本，那么您可以将其用于XML路径和其他技巧:

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,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.

FOR XML路径(")基本上将字符串连接到一个长XML结果中(比如code1、code2、code3等)，它在第一个字符上放置一个“无”字符，例如删除“多余的”第一个逗号，以得到您可能正在寻找的结果。

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 &.

更新:好的——我理解注释——如果您的数据库表中的文本已经包含<、>或&之类的字符，那么我当前的解决方案实际上会将它们编码到< >和&amp中。

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.

如果您对XML编码有问题，那么您必须看看@KM提出的解决方案，它也适用于这些字符。我要提醒大家一点:这种方法需要大量的资源和处理——正如你所知道的。

#2

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

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

#3

@AlexanderMP's answer is correct, but you can also consider handling nulls with coalesce:

@AlexanderMP的回答是正确的，但是您也可以考虑使用合并来处理null:

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

#4

For SQL Server 2005 and above use Coalesce for nulls and I am using Cast or Convert if there are numeric values -

对于SQL Server 2005和以上使用合并为nulls，如果有数值，我将使用Cast或Convert。

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

#5

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语句中的变量来连接值(即计算聚合值)。可能出现意外的查询结果。这是因为选择列表中的所有表达式(包括赋值)都不能保证每个输出行只执行一次。

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返回的行要多

#6

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:

这是使用简单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

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 NULL case issues that I can think of and also we fixed an error that we got for numeric values (field Sorting).

注意，我们已经解决了我能想到的所有可能的空情况问题，并且修复了数值(字段排序)的错误。

#1