LISTAGG aggregate function

The LISTAGG function aggregates a set of string elements into one string by concatenating the strings. Optionally, a separator string can be provided which is inserted between contiguous input strings.

Read syntax diagramSkip visual syntax diagramLISTAGG( ALLDISTINCTstring-expression ,separator ) WITHIN GROUP(ORDER BY,sort-keyASCDESC)

The schema is SYSIBM.

The LISTAGG function aggregates a set of string values for the group into one string by appending the string-expression values based on the order specified in the WITHIN GROUP clause.

The function is applied to the set of values that are derived from the first argument by the elimination of null values. If DISTINCT is specified, duplicate string-expression values are eliminated. If a separator argument is specified that is not the null value, the value is inserted between each pair of non-null string-expression values.

string-expression
An expression that specifies the string values to be aggregated. The expression must return a built-in character string, graphic string, binary string, numeric value, Boolean value, or datetime value:
  • If the value is not a character, graphic, or, binary string, or if it is a CLOB, it is implicitly cast to VARCHAR before the function is evaluated.
  • If the value is a DBCLOB, it is implicitly cast to VARGRAPHIC before the function is evaluated.
  • The value cannot be a BLOB (SQLSTATE 42815).
separator
A constant expression that defines the separation string that is used between non-null string-expression values. The expression must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to VARCHAR before the function is evaluated. CLOB and DBCLOB are supported through implicit casting. If the value is a CLOB, it is implicitly cast to VARCHAR before the function is evaluated. If the value is a DBCLOB, it is implicitly cast to VARGRAPHIC before the function is evaluated. The data type of separator cannot be a BLOB (SQLSTATE 42815).

The separator can be a constant, special register, variable, or an expression based on constants, special registers, or variables, provided that the expression does not include a non-deterministic function or a function that takes external action.

WITHIN GROUP
Indicates that the aggregation will follow the specified ordering within the grouping set.

If WITHIN GROUP is not specified and no other LISTAGG, ARRAY_AGG, or XMLAGG is included in the same SELECT clause with ordering specified, the ordering of strings within the result is not deterministic. If WITHIN GROUP is not specified, and the same SELECT clause has multiple occurrences of XMLAGG, ARRAY_AGG, or LISTAGG that specify ordering, the same ordering is used for the result of the LISTAGG function invocation.

ORDER BY
Specifies the order of the rows from the same grouping set that are processed in the aggregation. If the ORDER BY clause cannot distinguish the order of the column data, the rows in the same grouping set are arbitrarily ordered.
sort-key
The sort key can be a column name or a sort-key-expression. If the sort key is a constant, it does not refer to the position of the output column (as in the ORDER BY clause of a query); it is a constant, which implies no sort key.
ASC
Processes the sort-key in ascending order. This is the default option.
DESC
Processes the sort-key in descending order.

Result

The result data type of LISTAGG is based on the data type of string-expression:
Table 1. Determining the result data type and length
Data type of string-expression Result data type and length
CHAR(n) or VARCHAR(n) VARCHAR(MAX(4000, n))
GRAPHIC(n) or VARGRAPHIC(n) VARGRAPHIC(MAX(2000, n))
BINARY(n) or VARBINARY(n) VARBINARY(MAX(4000, n))
The string unit of the result data type is the same as the string units of the data type of string-expression.
The result data type can exceed VARCHAR(4000), VARBINARY(4000), or VARGRAPHIC(2000) if a derived size is used to determine the size of the result, to a maximum for the result data type. The following example successfully yields a return data type of VARCHAR(10000):
   LISTAGG(CAST(NAME AS VARCHAR(10000)), ',')

If the actual length of the aggregated result string exceeds the maximum for the result data type, an error is returned (SQLSTATE 22001).

The result can be null. If the function is applied to an empty set or all of the string-expression values in the set are null values, the result is a null value.

Rules

  • If DISTINCT is specified for LISTAGG, the sort-key of the ORDER BY specification must match string-expression (SQLSTATE 42822). If string-expression is implicitly cast, the sort-key must explicitly include a corresponding matching cast specification.
  • If a SELECT clause includes an ARRAY_AGG function, then all invocations of ARRAY_AGG, LISTAGG, XMLAGG, and XMLGROUP functions in the same SELECT clause must meet one of the following criteria (SQLSTATE 428GZ):
    • Specify the same order
    • Not specify an order
    • Have the string-expression argument of a LISTAGG with DISTINCT match the sort-key expression of the ORDER BY clause in ARRAY_AGG
  • LISTAGG cannot be used as part of an OLAP specification (SQLSTATE 42887).

Example

Produce an alphabetical list of comma-separated names, grouped by department.
   SELECT workdept, 
         LISTAGG(lastname, ', ') WITHIN GROUP(ORDER BY lastname)
         AS employees 
      FROM emp 
      GROUP BY workdept