listagg — Rows to Delimited Strings

The listagg function transforms values from a group of rows into a list of values that are delimited by a configurable separator. Listagg is typically used to denormalize rows into a string of comma-separated values (CSV) or other comparable formats suitable for human reading.

Listagg does not apply any escaping: it is not generally possible to tell whether an occurrence of the separator in the result is an actual separator, or just part of a value. The safe use of listagg for electronic data interfaces is therefore limited to cases in which an unambiguous separator can be selected, e.g. when aggregating numbers, dates, or strings that are known to not contain the separator.

When implementing electronic data interfaces, arrays and document types (JSON, XML) are advantageous as they offer type safety, or at least proper escaping.

Syntax

Listagg is an ordered set function, which require the within group clause to specify an order. The minimal syntax is:

LISTAGG(<expression>, <separator>) WITHIN GROUP(ORDER BY …)

The <expression> must not contain window functions, aggregate functions or subqueries.0 The standard only allows character literals in <separator>—i.e. no expression and no bind parameter.1 Bind parameters are nevertheless well supported in practice.

Listagg removes null values before aggregation2 like most other aggregate functions. If no not null value remains, the result of listagg is null. If needed, coalesce can be used to replace null values before aggregation.

The on overflow clause

The return type of listagg is either varchar or clob with an implementation defined length limit.3 In practice, it is a varchar type.4

Listagg accepts the optional on overflow clause to define the behavior if the result exceeds the length limit of the return type:

LISTAGG(<expression>, <separator> ON OVERFLOW …)

The default is on overflow error. In this case, the standard requires an exception with SQLSTATE 22001 to be raised5—in practice, this requirement is not fulfilled.

The on overflow truncate clause prevents the overflow by only concatenating as many values as the result type can accommodate. Furthermore, the on overflow truncate clause allows one to specify how the result is terminated:

ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT

The optional <filler> defaults to three periods (...) and will be added as last element if truncation happens.

If with count is specified and truncation happens, the number of omitted values is put in brackets and appended to the result.

The SQL standard does not require a warning to be issued on truncation.6 To know whether the result is complete or not, users can parse the result7 or compare the actual length of the result to the calculated length for a result containing all values.8

distinct

The listagg function accepts the optional set quantifiers all and distinct:

LISTAGG( [ALL|DISTINCT] <expression>, <separator> …) …

If neither is specified, all is default. If distinct is specified, duplicate values are removed before aggregation. Note that the elimination of duplicates is subject to the collation in effect.

Distinct can be implemented manually by removing duplicates before aggregation—e.g. in a subquery. This works for databases not supporting distinct in listagg, and also allows to keep a particular occurrence if duplicates exist.

The following example demonstrates this approach. The columns g and o represent the group by and order by keys respectively. The example uses min(o) to keep the first occurrence in case one value appears multiple times.

SELECT g
     , LISTAGG(val, ',') WITHIN GROUP (ORDER BY o) list
  FROM (SELECT g, min(o) o, val
          FROM dist_listagg
         GROUP BY g, val
       ) dt
 GROUP BY g

Combining listagg with filter and over

Listagg can be combined with the filter and over clauses:

LISTAGG(…) WITHIN GROUP(…) [FILTER(WHERE …)] [OVER(…)]

The effect of the filter clause is to remove rows before aggregation. Case can be used for the same effect.

The over clause must not contain an order by clause10 because the mandatory within group clause must contain an order by clause anyway. It is not possible to narrow the window frame: the set of aggregated rows is always the full partition.

Compatibility

Listagg was introduced with SQL:2016 as optional feature T625. Even though listagg is not yet widely supported, most databases offer similar functionality using a proprietary syntax.

1. Since 12.2
2. If ordered by the aggregated values: listagg(distinct X,…) within group (order by X)
3. SQLSTATE 54006
4. SQLSTATE 72000

Standard-Conforming Alternatives

1. No order by clause
2. No order by clause • Backported to 5.7.22
3. Has proprietary json_agg
4. Proprietary syntax available: FOR JSON AUTO
5. Proprietary syntax available: json_group_array (requires the JSON1 extension)
6. Proprietary syntax available: FOR XML PATH
7. When skipping the recursive keyword

Arrays

If the query does not strictly require the return of a delimited string, arrays can be used to return an array of values. An array can be constructed using the array_agg aggregate function or via a subquery.

ARRAY_AGG(<expression> ORDER BY …)

ARRAY(<query>)

In the second form, <query> can contain distinct and fetch first to remove duplicates and limit the array length.

Neither of the two approaches performs an implicit cast: the array elements have the same type as <expression>. That means that the retrieving application can fetch the values in a type-safe manner and apply formatting if required.

The type-safe nature of arrays allows them to also carry null values in an unambiguous way. Array_agg does therefore not remove null values like other aggregate functions do (including listagg).11

The filter clause can be used to remove null values before aggregation with array_agg.12 If the filter clause removes all rows, array_agg returns null—not an empty array.

The subquery syntax allows removing null values in the where clause of the <query> and returns an empty array if the subquery doesn’t return any rows.

If the order of elements is irrelevant, multisets and collect can also be used to pass a type-safe list to an application.

Document Types

Similar to array_agg, the SQL standard defines aggregate functions that return JSON or XML fragments: i.e. json_arrayagg and xmlagg. The main benefit compared to listagg is that they apply the respective escape rules.

JSON_ARRAYAGG(<expression> ORDER BY … [NULL ON NULL])

XMLAGG(XMLELEMENT(NAME <element-name>, <expression>) ORDER BY …)

Using with recursive

The following special case can be implemented using only with recursive and intermediate SQL-92:

LISTAGG(DISTINCT <expr1>, <sep> …) WITHIN GROUP(ORDER BY <expr1>)

Note the distinct and that <expr1> has to be the exact same expression in both cases.

The following example uses g as group by key, val as <expr1> and ', ' as <sep>:

WITH RECURSIVE
list_agg(g, val, list)
AS (
    SELECT g, min(val), CAST(null AS VARCHAR(255))
      FROM listagg_demo
     GROUP BY g
 UNION ALL
    SELECT prev.g
         , (SELECT min(val)
              FROM listagg_demo this
             WHERE this.g   = prev.g
               AND this.val > prev.val
           ) val
         , COALESCE(list || ', ', '') || val
      FROM list_agg prev
     WHERE prev.val IS NOT NULL
)
SELECT g, list
  FROM list_agg
 WHERE val IS NULL
 ORDER BY g

This particular implementation uses the “loose index scan” technique as explained on the PostgreSQL Wiki. The performance will remain at a rather low level even with an index on (g, val). The distinct behavior is a side effect of this technique.

The correct handling of null in val is an important special case: although null is generally ignored in aggregations, a group that consists of null values only must still be present in the result. This means that null must not be removed if there is no not null value in the group. The implementation above uses min(val) in the non-recursive expression to get this behavior.13

A more generic implementation that supports all semantics and arbitrary order by clauses is possible using with recursive and window functions. Aaron Bertrand’s post “Grouped Concatenation in SQL Server” presents an example of this approach.

In both cases, arbitrary on overflow behavior can be implemented.14

Proprietary Extensions

The only useful extension that is commonly available is the support of bind parameters and constant expressions in <separator>.

The standard neither allows omitting the <separator> nor omitting the within group clause. Yet some databases treat them as optional and apply implementation defined defaults or expose undefined behavior if within group is omitted.

Proprietary Alternatives

There are two widely available proprietary alternatives to listagg: group_concat and string_agg. Even though some databases use the same proprietary function name, they still use a different syntax.

The good news is that the proprietary functions have the same default semantic as listagg: they filter null values before aggregation but don’t remove duplicates (all semantics).

1. Accepts optional limit clause: string_agg(<e>, <s> ORDER BY … LIMIT <n>)

string_agg — PostgreSQL & Google BigQuery Syntax

PostgreSQL-style string_agg supports distinct and follows the array_agg syntax to specify an order:

STRING_AGG([ALL|DISTINCT] <expression>, <separator> [ORDER BY …])

Google BigQuery also supports this variant, extended by an optional limit clause after the order by.

PostgreSQL also offers a proprietary function to turn arrays into delimited strings: array_to_string.

string_agg — SQL Server Syntax (since release 2017/V14)

SQL Server’s string_agg doesn’t support distinct and uses the within group clause to specify an order:

STRING_AGG(<expression>, <separator>) [WITHIN GROUP (ORDER BY …)]

Aaron Bertrand’s post “Grouped Concatenation in SQL Server” demonstrates many other ways to implement listagg in older SQL Server releases—including the FOR XML PATH approach.

group_concat — MySQL and MariaDB Syntax

Group_concat supports distinct and expects the order by clause before the optional separator (default: comma):

GROUP_CONCAT([DISTINCT] <expression>
             ORDER BY …
             [SEPARATOR <separator>]
             [LIMIT <n>] -- MariaDB 10.3+
            )

Group_concat truncates the result if it exceeds a configurable maximum length and doesn’t honor element boundaries when doing so. In other words, it might truncate in the middle of an element.

MariaDB supports the same syntax and offers the optional limit clause in group_concat since version 10.3.

group_concat — SQLite Syntax

SQLite’s group_concat supports distinct but not order by:

GROUP_CONCAT([DISTINCT] <expression>, <separator>)

User-Defined Aggregates

Some databases support user-defined aggregate functions: MySQL (only in C/C++), Oracle Database (also WM_CONCAT), PostgreSQL, SQL Server (using CLR).