SQLSERVER 中的 ListAGG

我试图在 SQLServer 中聚合一个“ STRING”字段。我想找到与 Oracle 中相同的函数 LISTAGG。

你知道如何做同样的函数或另一种方法吗？

例如,

Field A | Field B
1       |  A
1       |  B
2       |  A

我希望这个查询的结果是

1 | AB
2 | A
198916 次浏览
小开
最佳答案

Starting in SQL Server 2017 the STRING_AGG function is available which simplifies the logic considerably:

select FieldA, string_agg(FieldB, '') as data
from yourtable
group by FieldA

See SQL Fiddle with Demo

In SQL Server you can use FOR XML PATH to get the result:

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;

See SQL Fiddle with Demo

小开

MySQL

SELECT FieldA
, GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;

Oracle & DB2

SELECT FieldA
, LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;

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

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)

  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

Ordering requires a CTE or subquery

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

In SQL Server 2017 STRING_AGG is added:

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_SPLIT is usefull for the opposite case and available in SQL Server 2016

小开

This might be useful to someone also, i.e. for data analysis and data profiling type of purposes. (i.e. not grouped by).

Prior to the SQL Server 2017 STRING_AGG function existing ..

(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

It returns comma separated values - e.g. "A,B".


提交答案