How to Replicate Oracle's LISTAGG Function in SQL Server?

Simulating Oracle’s LISTAGG function in SQL Server

SQL Server itself does not contain the LISTAGG function, but similar functions can be achieved through various methods.

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 (other versions)

For code readability and maintainability, common table expressions (CTE) are used here:

<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

When sorting is required, you need to use CTE or subquery

<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>

When no sorting is required

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

The above is the detailed content of How to Replicate Oracle's LISTAGG Function in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!