LISTAGG

Purpose

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

Syntax

listagg::=

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
DEPARTMENT LISTAGG_RESULT
ACCOUNTS 2005, 2003, 2002, 2004, 2001
HR 1003, 1002, 1004, 1001

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
ID DEPARTMENT HIRE_DATE LISTAGG_RESULT
2005 ACCOUNTS 2013-01-01 2005,2003
2003 ACCOUNTS 2015-07-01 2005,2003,2002
2002 ACCOUNTS 2017-01-01 2003,2002,2004
2004 ACCOUNTS 2017-01-01 2002,2004,2001
2001 ACCOUNTS 2018-07-01 2004,2001
1003 HR 2014-01-01 1003,1002
1002 HR 2016-01-01 1003,1002,1004
1004 HR 2016-01-01 1002,1004,1001
1001 HR 2018-01-01 1004,1001
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
PART ORD LISTAGG_RES
1 1 abcd
1 2 abcd, efgh
1 3 abcd, efgh...(1)
1 4 abcd, efgh...(2)
2 1 123
2 2 123, 456, 789
2 2 123, 456, 789