在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，所以请在解释太多的一边犯错:)

188717 次浏览
小开
最佳答案

没有真正简单的方法来做到这一点。不过，有很多想法。

我找到的最好的:

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)
小开

使用下面的代码，您必须在部署之前在您的项目属性上设置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);
}
}

我已经使用如下查询进行了测试:

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

小开

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

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的回答，怎么样:

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

顺便问一下，“姓氏”的使用是一个拼写错误还是我没有理解一个概念?

无论如何，非常感谢大家，因为这节省了我很多时间:)

小开

尝试了这些，但对于我在MS SQL Server 2005中的目的，以下是最有用的，我在xaprb找到的

declare @result varchar(8000);


set @result = '';


select @result = @result + name + ' '


from master.dbo.systypes;


select rtrim(@result);

@Mark，正如你提到的，空格字符给我带来了问题。

小开

我可能有点晚了，但这个方法对我来说是有效的，比COALESCE方法更容易。

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

要连接拥有多个项目经理的项目经理的所有名称，请编写如下命令:

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
小开

看看Github上的GROUP_CONCAT项目，我认为我做的正是你正在寻找的:

该项目包含一组SQLCLR用户定义聚合函数(SQLCLR UDAs)，它们共同提供与MySQL GROUP_CONCAT函数类似的功能。有多种功能，以确保基于所需功能的最佳性能…

小开

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

STRING_AGG ( expression, separator)。

连接字符串表达式的值并放置分隔符 它们之间的值。

.

.

.

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

我通常在接受的答案中使用XML方法。

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

FOR XML不能序列化数据…因为它 包含XML中不允许的字符(0x001D)

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

给变量赋值的方法是没有保证的，在生产代码中应该避免。

小开

对于我的谷歌同事们，这里有一个非常简单的即插即用的解决方案，在我与更复杂的解决方案斗争了一段时间后，它为我工作:

SELECT
distinct empName,
NewColumnName=STUFF((SELECT ','+ CONVERT(VARCHAR(10), projID )
FROM returns
WHERE empName=t.empName FOR XML PATH('')) , 1 , 1 , '' )
FROM
returns t

注意，我必须将ID转换为VARCHAR，以便将其作为字符串连接起来。如果你不需要这样做，这里有一个更简单的版本:

SELECT
distinct empName,
NewColumnName=STUFF((SELECT ','+ projID
FROM returns
WHERE empName=t.empName FOR XML PATH('')) , 1 , 1 , '' )
FROM
returns t
所有的功劳都在这里: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9508abc2-46e7-4186-b57f-7f368374e084/replicating-groupconcat-function-of-mysql-in-sql-server?forum=transactsql < / p >
小开

对于SQL Server 2017+，使用STRING_AGG()函数

    SELECT STRING_AGG(Genre, ',') AS Result
FROM Genres;

结果:样本

结果< h2 id = "结果" > < / h2 >

摇滚,爵士,乡村音乐,流行音乐,蓝调,嘻哈,说唱,朋克

小开

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;

提交答案