sql - SQLSERVER 中的 ListAGG

Question

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

您知道如何执行相同的功能或其他方法吗？

例如，

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

我希望这个查询的结果是

1 | AB
2 | A

Answer 1

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 服务器

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 是为了鼓励 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

排序需要 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;

无需订购

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