Definition:
The Oracle PL/SQL LISTAGG function orders data within each group specified in the ORDER BY clause and then concatenates the values of the <measure> column.
LISTAGG is used to perform string aggregation natively (the grouping and concatenation of multiple rows of data into a single row per group).
As with most aggregate functions, LISTAGG can be converted to an analytic function by adding the OVER() clause.
LISTAGG can use a range of constants or expressions as a delimiter for the aggregated strings. The delimiter is actually optional and can be excluded altogether if desired.
Note that the results of LISTAGG are constrained to the maximum size of VARCHAR2 (i.e. 4000). There is no CLOB or larger VARCHAR2 equivalent- for larger strings you will need to use an alternative means of gathering the elements (such as a collection or a user-defined PL/SQL function).
Aggregate Set Functionality
As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.
The arguments to the function are subject to the following rules:
- The measure_expr can be any expression. Null values in the measure column are ignored.
- The delimiter_expr designates the string that is to separate the measure values. This clause is optional and defaults to NULL.
- The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.
- The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2.
