LISTAGG Function

The LISTAGG function aggregates a set of string elements into one string by concatenating the strings.


1 LISTAGG
2  (
2 
3.1! ALL
3.1 DISTINCT
2  string-expression ,
3.1 separator
2 )
2?  WITHIN GROUP (
3.1! KEEP
3.1 ORDER BY
2 )
2?  CREATE (
3.1! YES
3.1 NO
2 )

1  LISTAGG ( 
2.1! ALL
2.1 DISTINCT
1 string-expression
1 ,
1 Separator
4 
5.1 )
4?  AVAIL (
5.1! KEEP
5.1 RESET
5.1 NO
5.1 YES
4 )

Syntax

  
          +------ ALL -----+
          |                |
LISTAGG(--+----------------+-- string-expression, separator)-------------->    
          |                |
          +--- DISTINCT ---+

       -->+-----------------------------------------------------------+--->
          |                                                           |
          |                           +---<----- , ------<-----+      |
          V                           |                        |      ^
          |                           V          +-- ASC  --+  ^      |
          |                           |          |          |  |      |
          +-- WITHIN GROUP (ORDER BY -+-sort-key-+----------+--+- )---+
                                                 |          |
                                                 +-- DESC --+

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 that are derived from the first argument by the elimination of null values. If DISTINCT is specified, duplicate string-expression values are eliminated. 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.

string-expression

An expression that specifies the string values to be aggregated. The expression must return a built-in character string, numeric value, Boolean value, or datetime value:
  • If the value is not a character, it is implicitly cast to LVARCHAR before the function is evaluated.
  • The value cannot be a Serial (which you can cast to INTEGER)
  • The Value cannot be a BLOB.

separator

A constant expression that defines the separation string that is used between non-null string-expression values. The expression must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to LVARCHAR before the function is evaluated. Only 255 bytes are allowed.

WITHIN GROUP

Indicates that the aggregation will follow the specified ordering within the grouping set.

If WITHIN GROUP is not specified and no other LISTAGG 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 LISTAGG that specify ordering, the same ordering is used for the result of the LISTAGG function invocation.

ORDER BY

Specifies the order of the rows from the same grouping set that are processed in the aggregation. If the ORDER BY clause cannot distinguish the order of the column data, the rows in the same grouping set are arbitrarily ordered.

sort-key

The sort key can be a column name or a sort-key-expression. If the sort key is a constant, it does not refer to the position of the output column (as in the ORDER BY clause of a query); it is a constant, which implies no sort key.

ASC

Processes the sort-key in ascending order. This is the default option.

DESC

Processes the sort-key in descending order.

Result

The result data type of LISTAGG is distinct type onedb.smartvarchar as longlvarchar(80). If the actual length of the aggregated result string exceeds 80 bytes, it is stored as CLOB in the database.

Limitations

  • If DISTINCT is specified for LISTAGG, the sort-key of the ORDER BY specification must match string-expression. If string-expression is implicitly cast, the sort-key must explicitly include a corresponding matching cast specification.
  • If a SELECT clause includes an LISTAGG function, then all invocations of LISTAGG in the same order.
  • LISTAGG does not support OLAP clause.