在Microsoft 2005中模拟group_concat MySQL函数？

Question

我正在尝试将一个基于MySQL的应用程序迁移到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

因此，我想知道的是:是否可以在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

我可能有点晚了，但这种方法对我有用，比合并方法更容易。

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

Answer 3

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

STRING_AGG ( expression, separator)。

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

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

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

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

因为XML无法序列化数据..。因为它包含一个在XML中不允许的字符(0x001D)。

能够处理所有字符的更健壮的方法是使用CLR聚合。但是，使用这种方法对级联元素应用排序更困难。

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