如何在SQL Server複製Oracle的LISTAGG函數？

在SQL Server中模擬Oracle的LISTAGG函數

SQL Server本身並不包含LISTAGG函數，但可以透過多種方法實現類似的功能。

MySQL

<code class="language-sql">SELECT
  FieldA,
  GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs
FROM
  TableName
GROUP BY
  FieldA
ORDER BY
  FieldA;</code>

Oracle & DB2

<code class="language-sql">SELECT
  FieldA,
  LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
FROM
  TableName
GROUP BY
  FieldA
ORDER BY
  FieldA;</code>

PostgreSQL

<code class="language-sql">SELECT
  FieldA,
  STRING_AGG(FieldB, ',' ORDER BY FieldB) AS FieldBs
FROM
  TableName
GROUP BY
  FieldA
ORDER BY
  FieldA;</code>

SQL Server

SQL Server >= 2017 & Azure SQL

<code class="language-sql">SELECT
  FieldA,
  STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
FROM
  TableName
GROUP BY
  FieldA
ORDER BY
  FieldA;</code>

SQL Server (其他版本)

為了程式碼的可讀性和可維護性，這裡使用公用表表達式 (CTE)：

<code class="language-sql">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;</code>

SQLite

需要排序時，需使用CTE或子查詢

<code class="language-sql">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;</code>

無排序時

<code class="language-sql">SELECT
  FieldA,
  GROUP_CONCAT(FieldB, ',') AS FieldBs
FROM
  TableName
GROUP BY
  FieldA
ORDER BY
  FieldA;</code>

以上是如何在SQL Server複製Oracle的LISTAGG函數？的詳細內容。更多資訊請關注PHP中文網其他相關文章！