LISTAGG¶

Syntax¶

Aggregate function

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

Window function

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )

Arguments¶

expr1

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.

expr2

This expression is used to group the rows in partitions.

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 you specify DISTINCT and WITHIN GROUP, both must refer to the same column. For example:

  SELECT listagg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
  

  Copy

  If you specify different columns for DISTINCT and WITHIN GROUP, an error occurs:

  SELECT listagg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
  

  Copy

  SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression
  

  Copy

  You must either specify the same column for DISTINCT and WITHIN GROUP or omit DISTINCT.

• With regard to NULL or empty input values:

  • 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.

• When used as a window function, this function does not support:

  • ORDER BY sub-clause in the OVER() clause.

  • Window frames.

Collation Details¶

• The collation of the result is the same as the collation of the input.

• Elements inside the list are ordered according to collations, if the ORDER BY sub-clause specified an expression with collation.

• The delimiter can not use a collation specification.

• Specifying collation inside ORDER BY does not impact the collation of the result. For example, the statement below contains two ORDER BY clauses, one for LISTAGG and one for the query results. Specifying collation inside the first one does not affect the collation of the second one. If you need to collate the output in both ORDER BY clauses, you must specify collation explicitly in both clauses.

  select listagg(x, ', ') within group (ORDER BY last_name collate 'sp')
      from table1
      ORDER BY last_name;
  

  Copy

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 |
---------------+--------------------------------------------------------------------+

The following example shows collation with LISTAGG. Note the difference in output order with the different collation specifications.

-- Collation
SELECT LISTAGG(spanish_phrase, '|') 
        WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'sp'))
    FROM collation_demo
    GROUP BY english_phrase;
+---------------------------------------------------------------+
| LISTAGG(SPANISH_PHRASE, '|')                                  |
|         WITHIN GROUP (ORDER BY COLLATE(SPANISH_PHRASE, 'SP')) |
|---------------------------------------------------------------|
| piña colada|Pinatubo (Mount)|pint|Pinta                       |
+---------------------------------------------------------------+
-- Different collation.
SELECT LISTAGG(spanish_phrase, '|') 
        WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8'))
    FROM collation_demo
    GROUP BY english_phrase;
+-----------------------------------------------------------------+
| LISTAGG(SPANISH_PHRASE, '|')                                    |
|         WITHIN GROUP (ORDER BY COLLATE(SPANISH_PHRASE, 'UTF8')) |
|-----------------------------------------------------------------|
| Pinatubo (Mount)|Pinta|pint|piña colada                         |
+-----------------------------------------------------------------+

Copy