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

Question

在Microsoft SQL Server 2005中模拟group_concat MySQL函数？我正在尝试将基于MySQL的应用程序迁移到Microsoft SQL Server 2005（不是选择，而是生活）。在原始应用程序中，我们几乎完全使用符合ANSI-SQL的语句，但有一个重要的例外 - 我们使用MySQL的group_concat功能相当频繁。group_concat顺便说一句，这样做：给出一张表，比如说，员工姓名和项目......SELECT empName, projID FROM project_members;收益：ANDY   |  A100ANDY   |  B391ANDY   |  X010TOM    |  A100TOM    |  A510...这是你用group_concat得到的：SELECT     empName, group_concat(projID SEPARATOR ' / ') FROM     project_members GROUP BY     empName;收益：ANDY   |  A100 / B391 / X010TOM    |  A100 / A510所以我想知道的是：是否有可能在SQL Server中编写用户定义的函数来模拟其功能group_concat？我几乎没有使用UDF，存储过程或类似的东西的经验，只是直接的SQL，所以请错误地说太多的解释:)

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)

Answer 2

我可能会晚一点，但这种方法适合我，比COALESCE方法更容易。

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

Answer 3

现在可能为时已晚，但这不是最简单的做事方式吗？

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

Answer 4

SQL Server 2017确实引入了新的聚合函数

STRING_AGG ( expression, separator)。

连接字符串表达式的值并在它们之间放置分隔符值。字符串末尾不添加分隔符。

可以通过附加来排序连接元素 WITHIN GROUP (ORDER BY some_expression)

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

然而，在某些情况下这可能会失败。例如，如果要连接的数据包含CHAR(29)您看到的内容

FOR XML无法序列化数据...因为它包含XML中不允许的字符（0x001D）。

可以处理所有字符的更健壮的方法是使用CLR聚合。然而，使用这种方法对连接元素应用排序更加困难。

分配给变量的方法不能保证，应该在生产代码中避免。

Answer 5

看看Github 上的GROUP_CONCAT项目，我想我的确是你正在寻找的：

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