在Microsoft SQL Server 2005中模拟MySQL的group_concat函数？

Question

我正在尝试将一个基于MySQL的应用迁移到Microsoft SQL Server 2005（这不是我的选择，但这就是生活）。

在原始应用程序中，我们几乎完全使用符合ANSI-SQL的语句，但有一个重要的例外--我们经常使用MySQL的group_concat函数。

顺便说一下，group_concat可以做到这一点：给定一个员工姓名和项目的表格...

SELECT empName, projID FROM project_members;

返回：

ANDY   |  A100
ANDY   |  B391
ANDY   |  X010
TOM    |  A100
TOM    |  A510

...这就是使用group_concat得到的结果：

SELECT 
    empName, group_concat(projID SEPARATOR ' / ') 
FROM 
    project_members 
GROUP BY 
    empName;

返回：

ANDY   |  A100 / B391 / X010
TOM    |  A100 / A510

那么我想知道的是：是否可以在SQL Server中编写用户定义函数来模拟group_concat的功能？

我几乎没有使用UDF、存储过程或类似内容方面的经验，只是直接使用SQL，所以请尽可能详细地解释：

- DanM

---

可能是重复的问题：如何使用SQL查询创建逗号分隔列表？ - 那篇文章更广泛，所以我会选择那篇作为规范。 - Tomas

可能是SQL Server中的SQL group_concat函数的重复问题。 - Trikaldarshiii

你如何确定列表排序的顺序，例如，你展示A100 / B391 / X010，但是在关系型数据库中没有隐含的排序方式，它也可以很容易地变成X010 / A100 / B391或任何其他组合。 - Steve Ford

如何在SQL Server中使用GROUP BY将字符串连接起来 - Salman A

可能是 如何在 SQL Server 中使用 GROUP BY 连接字符串？ 的重复问题。 - Salman A

Answer 1

没有真正简单的方法来做到这一点。尽管有很多想法。

我找到的最好的方法:

SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.columns AS extern
CROSS APPLY
(
    SELECT column_name + ','
    FROM information_schema.columns AS intern
    WHERE extern.table_name = intern.table_name
    FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY table_name, column_names;

或者是一个能够正确工作的版本，即使数据中可能包含像<这样的字符。

WITH extern
     AS (SELECT DISTINCT table_name
         FROM   INFORMATION_SCHEMA.COLUMNS)
SELECT table_name,
       LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names
FROM   extern
       CROSS APPLY (SELECT column_name + ','
                    FROM   INFORMATION_SCHEMA.COLUMNS AS intern
                    WHERE  extern.table_name = intern.table_name
                    FOR XML PATH(''), TYPE) x (column_names)
       CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names) 

- BradC

---

这个例子对我起作用了，但是我尝试做另一个聚合操作时它没有起作用，给了我一个错误提示：“在FROM子句中指定了多个相关名称'pre_trimmed'。” - PhilChuang

“pre_trimmed”只是该子查询的别名。子查询需要别名并且必须是唯一的，因此对于另一个子查询，请将其更改为其他唯一的名称... - Koen

你能展示一个没有使用table_name作为列名的例子吗？这样会让人感到困惑。 - S.Mason

Answer 2

我可能有点晚来参加这个派对，但是这个STUFF() + FOR XML 方法对我很有效，而且比COALESCE方法更简单。

SELECT STUFF(
             (SELECT ',' + Column_Name 
              FROM Table_Name
              FOR XML PATH (''))
             , 1, 1, '')

- Scott

---

这只是展示如何连接值 - group_concat按组连接它们更具挑战性（也是OP似乎需要的）。请参阅SO 15154644的已接受答案以了解如何执行此操作 - WHERE子句是关键添加。 - DJDave

@DJDave提到了这个答案。还可以参考类似问题的被接受答案。 - John Cummings

Answer 3

SQL Server 2017 引入了一个新的聚合函数 STRING_AGG (expression, separator)。该函数可以连接字符串表达式的值，并在它们之间放置分隔符，但不会在结尾添加分隔符。

通过追加 WITHIN GROUP (ORDER BY some_expression) 可以对连接后的元素进行排序。

对于2005-2016版本，我通常使用被接受的答案中的XML方法。

然而，在某些情况下，这可能会失败。例如，如果要连接的数据包含 CHAR(29)，则会出现以下错误：

FOR XML could not serialize the data ... because it contains a character (0x001D) which is not allowed in XML.

处理所有字符的更健壮的方法是使用 CLR 聚合函数。但是，对连接后的元素应用排序更加困难。

在生产代码中，变量赋值的方法不能保证，因此应避免使用。

- Martin Smith

---

现在Azure SQL中也可以使用此功能：https://azure.microsoft.com/zh-cn/roadmap/new-t-sql-string-functions-in-azure-sql-database/ - Simon_Weaver

Answer 4

可能现在已经太晚了，但这难道不是最简单的方法吗？

SELECT     empName, projIDs = replace
                          ((SELECT Surname AS [data()]
                              FROM project_members
                              WHERE  empName = a.empName
                              ORDER BY empName FOR xml path('')), ' ', REQUIRED SEPERATOR)
FROM         project_members a
WHERE     empName IS NOT NULL
GROUP BY empName

- J Hardiman

---

有趣。我已经完成了手头的项目，但我会尝试这种方法。谢谢！ - DanM

不错的技巧--唯一的问题是对于包含空格的姓氏，它会用分隔符替换空格。 - Mark Elliot

我自己也遇到了这样的问题，马克。不幸的是，在MSSQL跟上时代并引入GROUP_CONCAT之前，这是我能想到的最费力的方法之一，以满足这里所需的要求。 - J Hardiman

谢谢！这是一个 SQL Fiddle，展示了它的工作原理：http://sqlfiddle.com/#!6/c5d56/3 - fleed

Answer 5

请看Github上的GROUP_CONCAT项目，我认为它正是你所寻找的：

该项目包含一组SQLCLR用户定义的聚合函数（SQLCLR UDAs），它们共同提供类似于MySQL GROUP_CONCAT函数的功能。有多个函数可根据所需功能确保最佳性能...

- MaxiWheat

---

@MaxiWheat：很多人在点踩之前不仔细阅读问题或答案，这会直接影响到帖子所有者。这是因为他们的错误所导致的。 - Steve Lam

非常好用。我唯一缺少的功能是能够按列排序，就像MySQL中的group_concat()函数一样：GROUP_CONCAT(klascode,'(',name,')' ORDER BY klascode ASC SEPARATOR ', ')。 - Jan

Answer 6

要将所有拥有多个项目经理的项目的项目经理名称连接起来，请编写以下代码：

SELECT a.project_id,a.project_name,Stuff((SELECT N'/ ' + first_name + ', '+last_name FROM projects_v 
where a.project_id=project_id
 FOR
 XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N''
) mgr_names
from projects_v a
group by a.project_id,a.project_name

- Cmaly

Answer 7

在下面的代码中，在部署之前，您必须在项目属性上设置PermissionLevel=External，并更改数据库以信任外部代码（请务必在其他地方阅读有关安全风险和替代方案[如证书]的信息） ，方法是运行 ALTER DATABASE database_name SET TRUSTWORTHY ON。

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,
    MaxByteSize=8000,
    IsInvariantToDuplicates=true,
    IsInvariantToNulls=true,
    IsInvariantToOrder=true,
    IsNullIfEmpty=true)]
public struct CommaDelimit : IBinarySerialize
{
    [Serializable]
    private class StringList : List<string>
    { }

    private StringList List;

    public void Init()
    {
        this.List = new StringList();
    }

    public void Accumulate(SqlString value)
    {
        if (!value.IsNull)
            this.Add(value.Value);
    }

    private void Add(string value)
    {
        if (!this.List.Contains(value))
            this.List.Add(value);
    }

    public void Merge(CommaDelimit group)
    {
        foreach (string s in group.List)
        {
            this.Add(s);
        }
    }

    void IBinarySerialize.Read(BinaryReader reader)
    {
        IFormatter formatter = new BinaryFormatter();
        this.List = (StringList)formatter.Deserialize(reader.BaseStream);
    }

    public SqlString Terminate()
    {
        if (this.List.Count == 0)
            return SqlString.Null;

        const string Separator = ", ";

        this.List.Sort();
 
        return new SqlString(String.Join(Separator, this.List.ToArray()));
   }

   void IBinarySerialize.Write(BinaryWriter writer)
   {
        IFormatter formatter = new BinaryFormatter();
        formatter.Serialize(writer.BaseStream, this.List);
    }
}

我用一个类似以下查询的方式进行了测试：

I've tested this using a query that looks like:

SELECT 
 dbo.CommaDelimit(X.value) [delimited] 
FROM 
 (
  SELECT 'D' [value] 
  UNION ALL SELECT 'B' [value] 
  UNION ALL SELECT 'B' [value] -- intentional duplicate
  UNION ALL SELECT 'A' [value] 
  UNION ALL SELECT 'C' [value] 
 ) X 

它的输出是：A、B、C、D

- GregTSmith

Answer 8

我尝试了一些方法，但在我使用的 MS SQL Server 2005 中，以下方法是最有用的，我在 xaprb 上找到了相关内容。

declare @result varchar(8000);

set @result = '';

select @result = @result + name + ' '

from master.dbo.systypes;

select rtrim(@result);

正如你提到的那样，对我造成问题的是空格字符。

- isoughtajam

---

我认为这个引擎并不真正保证这种方法的任何顺序，因为变量是根据执行计划作为数据流进行计算的。尽管到目前为止它似乎大部分时间都能工作。 - phil_w

Answer 9

关于J Hardiman的回答，您觉得如何：

SELECT empName, projIDs=
  REPLACE(
    REPLACE(
      (SELECT REPLACE(projID, ' ', '-somebody-puts-microsoft-out-of-his-misery-please-') AS [data()] FROM project_members WHERE empName=a.empName FOR XML PATH('')), 
      ' ', 
      ' / '), 
    '-somebody-puts-microsoft-out-of-his-misery-please-',
    ' ') 
  FROM project_members a WHERE empName IS NOT NULL GROUP BY empName

顺便问一下，“Surname”的使用是打错了还是我理解有误？不管怎样，非常感谢你们，因为这节省了我很多时间 :)

- user422190

---

我觉得这是一个相当不友好的回答，作为一个答案并没有任何帮助。 - Tim Meers

只是现在才看到......我当时并不是有恶意的，那个时候我对 SQL Server 非常失望（现在仍然如此）。实际上，这篇帖子中的回答确实很有帮助；编辑：顺便问一下，为什么它对你没用呢？它对我来说可行啊。 - user422190

Answer 10

2021

@AbdusSalamAzad的回答是正确的。

SELECT STRING_AGG(my_col, ',') AS my_result FROM my_tbl;

如果结果过大，你可能会收到错误信息 "STRING_AGG聚合结果超出了8000字节的限制。使用LOB类型以避免结果截断。"，可以通过将查询更改为以下内容来解决：

SELECT STRING_AGG(convert(varchar(max), my_col), ',') AS my_result FROM my_tbl;

- evilReiko

---

已经有这么多答案了，我认为最好将您关于8000字节限制问题的完全有效的注释以及解决方案合并到首次建议使用STRING_AGG的答案中。 - Andriy M