在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   |  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，所以请错误地说太多的解释:)

Answer 1

没有真正简单的方法来做到这一点。不过，那里有很多想法。我找到的最好的一个：SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_namesFROM information_schema.columns AS externCROSS APPLY(&nbsp; &nbsp; SELECT column_name + ','&nbsp; &nbsp; FROM information_schema.columns AS intern&nbsp; &nbsp; WHERE extern.table_name = intern.table_name&nbsp; &nbsp; FOR XML PATH('')) pre_trimmed (column_names)GROUP BY table_name, column_names;或者，如果数据可能包含诸如的字符，则该版本可正常工作 <WITH extern&nbsp; &nbsp; &nbsp;AS (SELECT DISTINCT table_name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM&nbsp; &nbsp;INFORMATION_SCHEMA.COLUMNS)SELECT table_name,&nbsp; &nbsp; &nbsp; &nbsp;LEFT(y.column_names, LEN(y.column_names) - 1) AS column_namesFROM&nbsp; &nbsp;extern&nbsp; &nbsp; &nbsp; &nbsp;CROSS APPLY (SELECT column_name + ','&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM&nbsp; &nbsp;INFORMATION_SCHEMA.COLUMNS AS intern&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE&nbsp; extern.table_name = intern.table_name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FOR XML PATH(''), TYPE) x (column_names)&nbsp; &nbsp; &nbsp; &nbsp;CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names)

Answer 2

我可能会晚一点，但这种方法适合我，比COALESCE方法更容易。SELECT&nbsp;STUFF( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(SELECT&nbsp;','&nbsp;+&nbsp;Column_Name&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;Table_Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FOR&nbsp;XML&nbsp;PATH&nbsp;('')) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;1,&nbsp;1,&nbsp;'')

Answer 3

现在可能为时已晚，但这不是最简单的做事方式吗？SELECT&nbsp; &nbsp; &nbsp;empName, projIDs = replace&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ((SELECT Surname AS [data()]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM project_members&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE&nbsp; empName = a.empName&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY empName FOR xml path('')), ' ', REQUIRED SEPERATOR)FROM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;project_members aWHERE&nbsp; &nbsp; &nbsp;empName IS NOT NULLGROUP BY empName

Answer 4

SQL Server 2017确实引入了新的聚合函数STRING_AGG ( expression, separator)。连接字符串表达式的值并在它们之间放置分隔符值。字符串末尾不添加分隔符。可以通过附加来排序连接元素&nbsp;WITHIN GROUP (ORDER BY some_expression)对于2005-2016版本，我通常在接受的答案中使用XML方法。然而，在某些情况下这可能会失败。例如，如果要连接的数据包含CHAR(29)您看到的内容FOR XML无法序列化数据...因为它包含XML中不允许的字符（0x001D）。可以处理所有字符的更健壮的方法是使用CLR聚合。然而，使用这种方法对连接元素应用排序更加困难。分配给变量的方法不能保证，应该在生产代码中避免。

Answer 5

看看Github&nbsp;上的GROUP_CONCAT项目，我想我的确是你正在寻找的：该项目包含一组SQLCLR用户定义聚合函数（SQLCLR UDA），它们共同提供与MySQL GROUP_CONCAT函数类似的功能。根据所需功能，有多种功能可确保最佳性能......