DB2 Version 9.7 for Linux, UNIX, and Windows

LISTAGG aggregate function

Read syntax diagramSkip visual syntax diagram
>>-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

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