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.
>>-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.
The separator can be a literal, special register, variable, or an expression based on literals, special registers, or variables, provided that the expression does not include a non-deterministic function or a function that takes external action.
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.
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.
SELECT workdept,
LISTAGG(lastname, ', ') WITHIN GROUP(ORDER BY lastname)
AS employees
FROM emp
GROUP BY workdept