LISTAGG

Purpose

This function returns a concatenated string of values of expr from all rows in the window or group.

Syntax

Usage Notes

• With DISTINCT, the function considers duplicate values of expr only once. It considers all occurrences with ALL. If the function contains neither ALL nor DISTINCT the default is ALL.
• The WITHIN GROUP clause is optional. It specifies the order_clause for the sorting order of the rows within the window or group.
• It is only possible to specify the order_clause once, either in the WITHIN GROUP clause or as part of the over_clause.
• The ON OVERFLOW specifies the behavior in case of an overflow. An overflow occurs if the result string is longer than the maximum length supported by type VARCHAR (2 million characters).
• Without specification of an ON OVERFLOW clause, the default behavior is ON OVERFLOW ERROR. In this setting, Exasol raises an error if an overflow occurs.
• With the specification of ON OVERFLOW TRUNCATE clause, Exasol prevents an error in case of an overflow. Instead, it truncates the result string and appends the truncation_filler to remain within the data type size boundaries. Without the specification of truncation_filler, Exasol uses the default string value '...'.
• In combination with the WITH COUNT clause, the result string is concatenated with the count (i.e., the number of rows that were truncated) in the case of an overflow. The count is enclosed in parentheses. Exasol reserves 22 characters within the data type size boundaries for the count. This also includes the parentheses.

Examples

Aggregate Function

SELECT 
    department, 
    LISTAGG(id, ', ') WITHIN GROUP (ORDER BY hire_date) LISTAGG_RESULT 
FROM employee_table GROUP BY department;

Result

Analytic Function

SELECT 
    id, department, hire_date, 
    LISTAGG(id, ',') within group (ORDER BY hire_date) OVER (PARTITION BY department rows between 1 preceding and 1 following) LISTAGG_RESULT
FROM employee_table ORDER BY department, hire_date;

Result

CREATE OR REPLACE TABLE t (p DECIMAL(6, 0), o DECIMAL(6, 0), v VARCHAR(2000000));
INSERT INTO T VALUES (1, 1, 'abcd');
INSERT INTO T VALUES (1, 2, 'efgh');
INSERT INTO T VALUES (1, 3, repeat('z', 1999995));
INSERT INTO T VALUES (1, 4, 'ijkl');
INSERT INTO T VALUES (2, 1, '123');
INSERT INTO T VALUES (2, 2, '456');
INSERT INTO T VALUES (2, 2, '789');

SELECT 
    part, ord, 
    LISTAGG(v, ', ' ON OVERFLOW TRUNCATE WITH COUNT) 
        OVER (PARTITION BY part ORDER BY ord) LISTAGG_RES 
FROM t ORDER BY part, ord;

Result