在 Microsoft SQL Server 2005 中模拟 group_concat MySQL 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/451415/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simulating group_concat MySQL function in Microsoft SQL Server 2005?
提问by DanM
I'm trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that's life).
我正在尝试将基于 MySQL 的应用程序迁移到 Microsoft SQL Server 2005（不是自愿的，但这就是生活）。
In the original app, we used almostentirely ANSI-SQL compliant statements, with one significant exception -- we used MySQL's group_concatfunction fairly frequently.
在最初的应用程序中，我们几乎完全使用了符合 ANSI-SQL 的语句，只有一个重要的例外——我们group_concat相当频繁地使用 MySQL 的函数。
group_concat, by the way, does this: given a table of, say, employee names and projects...
group_concat，顺便说一句，这样做：给定一个表格，例如，员工姓名和项目......
SELECT empName, projID FROM project_members;
returns:
返回：
ANDY | A100
ANDY | B391
ANDY | X010
TOM | A100
TOM | A510
... and here's what you get with group_concat:
...这是您使用 group_concat 获得的结果：
SELECT
empName, group_concat(projID SEPARATOR ' / ')
FROM
project_members
GROUP BY
empName;
returns:
返回：
ANDY | A100 / B391 / X010
TOM | A100 / A510
So what I'd like to know is: Is it possible to write, say, a user-defined function in SQL Server which emulates the functionality of group_concat?
所以我想知道的是：是否可以在 SQL Server 中编写一个用户定义的函数来模拟group_concat?
I have almost no experience using UDFs, stored procedures, or anything like that, just straight-up SQL, so please err on the side of too much explanation :)
我几乎没有使用 UDF、存储过程或类似的东西的经验，只是直接的 SQL，所以请不要过多解释:)
采纳答案by BradC
No REAL easy way to do this. Lots of ideas out there, though.
没有真正简单的方法来做到这一点。不过，那里有很多想法。
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;
Or a version that works correctly if the data might contain characters such as <
或者如果数据可能包含诸如 <
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)
回答by Scott
I may be a bit late to the party but this method works for me and is easier than the COALESCE method.
我参加聚会可能有点晚了，但这种方法对我有用，而且比 COALESCE 方法更容易。
SELECT STUFF(
(SELECT ',' + Column_Name
FROM Table_Name
FOR XML PATH (''))
, 1, 1, '')
回答by J Hardiman
Possibly too late to be of benefit now, but is this not the easiest way to do things?
现在可能为时已晚，无法从中受益，但这不是最简单的做事方式吗？
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
回答by Martin Smith
SQL Server 2017does introduce a new aggregate function
SQL Server 2017确实引入了新的聚合函数
STRING_AGG ( expression, separator).
STRING_AGG ( expression, separator).
Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.
连接字符串表达式的值并在它们之间放置分隔符值。字符串末尾不添加分隔符。
The concatenated elements can be ordered by appending WITHIN GROUP (ORDER BY some_expression)
连接的元素可以通过附加来排序 WITHIN GROUP (ORDER BY some_expression)
For versions 2005-2016I typically use the XML method in the accepted answer.
对于 2005-2016 版本，我通常在接受的答案中使用 XML 方法。
This can fail in some circumstances however. e.g. if the data to be concatenated contains CHAR(29)you see
但是，这在某些情况下可能会失败。例如，如果要连接的数据包含CHAR(29)您看到
FOR XML could not serialize the data ... because it contains a character (0x001D) which is not allowed in XML.
FOR XML 无法序列化数据...因为它包含 XML 中不允许的字符 (0x001D)。
A more robust method that can deal with all characters would be to use a CLR aggregate. However applying an ordering to the concatenated elements is more difficult with this approach.
可以处理所有字符的更健壮的方法是使用 CLR 聚合。然而，使用这种方法对连接的元素应用排序更加困难。
The method of assigning to a variable is not guaranteedand should be avoided in production code.
分配给变量的方法是没有保证的，应该避免在生产代码中使用。
回答by MaxiWheat
Have a look at the GROUP_CONCATproject on Github, I think I does exactly what you are searching for:
看看Github上的GROUP_CONCAT项目，我想我做的正是你正在寻找的：
This project contains a set of SQLCLR User-defined Aggregate functions (SQLCLR UDAs) that collectively offer similar functionality to the MySQL GROUP_CONCAT function. There are multiple functions to ensure the best performance based on the functionality required...
该项目包含一组 SQLCLR 用户定义聚合函数 (SQLCLR UDA)，它们共同提供与 MySQL GROUP_CONCAT 函数类似的功能。有多种功能可确保基于所需功能的最佳性能...
回答by Cmaly
To concatenate all the project manager names from projects that have multiple project managers write:
要连接具有多个项目经理的项目中的所有项目经理姓名，请编写：
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
回答by isoughtajam
Tried these but for my purposes in MS SQL Server 2005 the following was most useful, which I found at xaprb
尝试了这些，但为了我在 MS SQL Server 2005 中的目的，以下是最有用的，我在xaprb找到了
declare @result varchar(8000);
set @result = '';
select @result = @result + name + ' '
from master.dbo.systypes;
select rtrim(@result);
@Mark as you mentioned it was the space character that caused issues for me.
@Mark 正如您所提到的，是空格字符给我带来了问题。
回答by GregTSmith
With the below code you have to set PermissionLevel=External on your project properties before you deploy, and change the database to trust external code (be sure to read elsewhere about security risks and alternatives [like certificates]) by running "ALTER DATABASE database_name SET TRUSTWORTHY ON".
使用以下代码，您必须在部署之前在项目属性上设置 PermissionLevel=External，并通过运行“ALTER DATABASE database_name SET”将数据库更改为信任外部代码（请务必在其他地方阅读有关安全风险和替代方案 [如证书]）值得信赖”。
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
And yields: A, B, C, D
并产生：A、B、C、D
回答by user422190
About J Hardiman's answer, how about:
关于 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
By the way, is the use of "Surname" a typo or am i not understanding a concept here?
顺便说一句，“姓氏”的使用是一个错字还是我在这里不理解一个概念？
Anyway, thanks a lot guys cuz it saved me quite some time :)
无论如何，非常感谢大家，因为它为我节省了很多时间:)
回答by krock
For my fellow Googlers out there, here's a very simple plug-and-play solution that worked for me after struggling with the more complex solutions for a while:
对于我的 Google 同事，这里有一个非常简单的即插即用解决方案，在与更复杂的解决方案苦苦挣扎了一段时间后对我有用：
SELECT
distinct empName,
NewColumnName=STUFF((SELECT ','+ CONVERT(VARCHAR(10), projID )
FROM returns
WHERE empName=t.empName FOR XML PATH('')) , 1 , 1 , '' )
FROM
returns t
Notice that I had to convert the ID into a VARCHAR in order to concatenate it as a string. If you don't have to do that, here's an even simpler version:
请注意，我必须将 ID 转换为 VARCHAR 才能将其连接为字符串。如果您不必这样做，这里有一个更简单的版本：
SELECT
distinct empName,
NewColumnName=STUFF((SELECT ','+ projID
FROM returns
WHERE empName=t.empName FOR XML PATH('')) , 1 , 1 , '' )
FROM
returns t
All credit for this goes to here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9508abc2-46e7-4186-b57f-7f368374e084/replicating-groupconcat-function-of-mysql-in-sql-server?forum=transactsql
这一切都归功于这里：https: //social.msdn.microsoft.com/Forums/sqlserver/en-US/9508abc2-46e7-4186-b57f-7f368374e084/replicating-groupconcat-function-of-mysql-in- sql-server?forum=transactsql
