Categories:
Aggregate Functions (General) , Window Functions

LISTAGG

Returns the concatenated input values, separated by the delimiter string.

Collation:Supported

Syntax

LISTAGG( [ DISTINCT ] <expr> [, <delimiter> ] ) [ WITHIN GROUP ( <orderby_clause> ) ]

Arguments

expr
The expression (typically a column name) that determines the values to be put into the list. The expression should evaluate to a string, or to a data type that can be cast to string.
delimiter

A string, or an expression that evaluates to a string. In practice, this is usually a single-character string. The string should be surrounded by single quotes, as shown in the examples below.

If no delimiter string is specified, the empty string is used as the delimiter.

The delimiter must be a constant.

orderby_clause An expression (typically a column name) that determines the order of the values in the list.

Returns

Returns a string that includes all of the non-NULL input values, separated by the delimiter.

(Note that this does not return a “list” (e.g. it does not return an ARRAY; it returns a single string that contains all of the non-NULL input values.)

Usage Notes

  • DISTINCT is supported for this function.
  • If you do not specify the WITHIN GROUP (<orderby_clause>), the order of elements within each list is unpredictable. (An ORDER BY clause outside the WITHIN GROUP clause applies to the order of the output rows, not to the order of the list elements within a row.)
  • If the input is empty, an empty string is returned.
  • If all input expressions evaluate to NULL, the output is an empty string.
  • If some but not all input expressions evaluate to NULL, the output contains all non-NULL values and excludes the NULL values.

Examples

SELECT listagg(O_ORDERKEY, ' ')
    FROM orders WHERE O_TOTALPRICE > 450000;

---------------------------------------------+
          LISTAGG(O_ORDERKEY, ' ')           |
---------------------------------------------+
 41445 55937 67781 80550 95808 101700 103136 |
---------------------------------------------+
SELECT listagg(DISTINCT O_ORDERSTATUS, '|')
    FROM orders WHERE O_TOTALPRICE > 450000;

--------------------------------------+
 LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
--------------------------------------+
 F|O                                  |
--------------------------------------+
SELECT O_ORDERSTATUS, listagg(O_CLERK, ', ') WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)
    FROM orders WHERE O_TOTALPRICE > 450000 GROUP BY O_ORDERSTATUS;

---------------+--------------------------------------------------------------------+
 O_ORDERSTATUS |  LISTAGG(O_CLERK, ', ') WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)  |
---------------+--------------------------------------------------------------------+
 O             | Clerk#000000220, Clerk#000000411, Clerk#000000114                  |
 F             | Clerk#000000508, Clerk#000000136, Clerk#000000521, Clerk#000000386 |
---------------+--------------------------------------------------------------------+