>>-LISTAGG--(--string-expression--+--------------+--)----------->
'-,--separator-'
>--+------------------------------------------------------+----><
| .-,------------------. |
| V .-ASC--. | |
'-WITHIN GROUP--(--ORDER BY----sort-key--+------+-+--)-'
'-DESC-'
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 derived from the first argument by the
elimination of null values. 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 returns a string that has data type of CHAR,
VARCHAR, GRAPHIC, or VARGRAPHIC.
- separator
- A constant expression that returns a string that has a data type
of CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC for use as a separator between
non-null string-expression values.
The
separator can be a literal, special register, variable, or an expression
based on literals, special registers, or variables as long as 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), but it is
simply 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.
The
result data type of LISTAGG is based on the data type of
string-expression:
- If the data type of string-expression is CHAR(n)
or VARCHAR(n), the data type of result is VARCHAR( MAX(4000, n))
- If the data type of string-expression is GRAPHIC(n or
VARGRAPHIC(n), the data type of result is VARGRAPHIC( MAX(2000, n))
The result data type can exceed VARCHAR(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
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 specify the same order or not specify an order
(SQLSTATE 428GZ).
- 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