SQL SQLSERVER 中的 ListAGG
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15477743/
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
ListAGG in SQLSERVER
提问by user1557642
I'm trying to aggregate a 'STRING' field in SQLServer. I would like to find the same function LISTAGG like in Oracle .
我正在尝试在 SQLServer 中聚合一个“字符串”字段。我想在 Oracle 中找到与 LISTAGG 相同的功能。
Do you know how to do the same function or an another method?
你知道如何做同样的功能或另一种方法吗？
For Example,
例如，
Field A | Field B
1 | A
1 | B
2 | A
And I would like that the result of this query will be
我希望这个查询的结果是
1 | AB
2 | A
回答by Manas Kumar
MySQL
MySQL
SELECT FieldA
, GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
Oracle & DB2
甲骨文和 DB2
SELECT FieldA
, LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
PostgreSQL
PostgreSQL
SELECT FieldA
, STRING_AGG(FieldB, ',' ORDER BY FieldB) AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
SQL Server
数据库服务器
SQL Server ≥ 2017 & Azure SQL
SQL Server ≥ 2017 & Azure SQL
SELECT FieldA
, STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
SQL Server ≤ 2016 (CTE included to encourage the DRY principle)
SQL Server ≤ 2016（包含 CTE 以鼓励DRY 原则）
WITH CTE_TableName AS (
SELECT FieldA, FieldB
FROM TableName)
SELECT t0.FieldA
, STUFF((
SELECT ',' + t1.FieldB
FROM CTE_TableName t1
WHERE t1.FieldA = t0.FieldA
ORDER BY t1.FieldB
FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
FROM CTE_TableName t0
GROUP BY t0.FieldA
ORDER BY FieldA;
SQLite
SQLite
Ordering requires a CTE or subquery
排序需要 CTE 或子查询
WITH CTE_TableName AS (
SELECT FieldA, FieldB
FROM TableName
ORDER BY FieldA, FieldB)
SELECT FieldA
, GROUP_CONCAT(FieldB, ',') AS FieldBs
FROM CTE_TableName
GROUP BY FieldA
ORDER BY FieldA;
Without ordering
无需订购
SELECT FieldA
, GROUP_CONCAT(FieldB, ',') AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
回答by Taryn
Starting in SQL Server 2017the STRING_AGGfunction is available which simplifies the logic considerably:
从SQL Server 2017开始，该STRING_AGG功能可用，大大简化了逻辑：
select FieldA, string_agg(FieldB, '') as data
from yourtable
group by FieldA
In SQL Server you can use FOR XML PATHto get the result:
在 SQL Server 中，您可以使用FOR XML PATH来获取结果：
select distinct t1.FieldA,
STUFF((SELECT distinct '' + t2.FieldB
from yourtable t2
where t1.FieldA = t2.FieldA
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,0,'') data
from yourtable t1;
回答by vldmrrdjcc
In SQL Server 2017 STRING_AGGis added:
在 SQL Server 2017 STRING_AGG中添加：
SELECT t.name,STRING_AGG (c.name, ',') AS csv
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
GROUP BY t.name
ORDER BY 1
Also, STRING_SPLITis usefull for the opposite case and available in SQL Server 2016
此外，STRING_SPLIT对于相反的情况很有用，可在 SQL Server 2016 中使用
回答by Allan F
This might be useful to someone also ..
这可能对某人也有用..
i.e. For a data analyst and data profiling type of purposes ..(i.e. not grouped by) ..
即对于数据分析师和数据分析类型的目的..（即不分组）..
Prior to the SQL*Server 2017 String_agg function existence ..
在 SQL*Server 2017 String_agg 函数存在之前..
(i.e. returns just one row ..)
（即只返回一行..）
select distinct
SUBSTRING (
stuff(( select distinct ',' + [FieldB] from tablename order by 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,1,0,'' )
,2,9999)
from
tablename
e.g. returns comma separated values A,B
例如返回逗号分隔值 A,B
