Purpose
The LISTAGG function is used to convert columns to rows. It sorts the data within each group specified by the ORDER BY clause and then concatenates the values of the measure column. It can be used as either an aggregate or analytic function.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for computation. This function partitions the query result set into groups based on one or more expressions in thequery_partition_clause. - When used as an aggregate function, you do not need to use the
OVERkeyword.- As a single-set aggregate function,
LISTAGGoperates on all rows and returns a single output row. - As a group aggregate,
LISTAGGoperates on each group defined by theGROUP BYclause and returns output rows.
- As a single-set aggregate function,
Syntax
LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)
[ OVER query_partition_clause ]
Parameters
Parameter |
Description |
|---|---|
| measure_expr | Any expression. Null values in the measure column are ignored. |
| delimiter | A string that specifies the delimiter for the measure values. This clause is optional, with a default value of NULL. |
| OVER | Use the OVER clause to define the window for computation. For more information, see Analytic Function Description. |
Return type
If the measure column is of type RAW, the return type is RAW; otherwise, the return type is VARCHAR2.
Examples
Assume that the employees table has been created.
obclient> SELECT * FROM employees;
+---------------+------------+-----------+------------+--------+
| DEPARTMENT_ID | MANAGER_ID | LAST_NAME | HIREDATE | SALARY |
+---------------+------------+-----------+------------+--------+
| 30 | 100 | Raphaely | 2017-07-01 | 1700 |
| 30 | 100 | De Haan | 2018-05-01 | 11000 |
| 40 | 100 | Errazuriz | 2017-07-21 | 1400 |
| 50 | 100 | Hartstein | 2019-10-05 | 14000 |
| 50 | 100 | Raphaely | 2017-07-22 | 1700 |
| 50 | 100 | Weiss | 2019-10-05 | 13500 |
| 90 | 100 | Russell | 2019-07-11 | 13000 |
| 90 | 100 | Partners | 2018-12-01 | 14000 |
+---------------+------------+-----------+------------+--------+
8 rows in set
Aggregate function example
Query all employees in department 30 and sort them by hire date and surname.
obclient> SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hiredate, last_name) as "Emp_list",
MIN(hiredate) as "Earliest"
FROM employees
WHERE department_id = 30;
+-------------------+------------+
| Emp_list | Earliest |
+-------------------+------------+
| Raphaely; De Haan | 2017-07-01 |
+-------------------+------------+
1 row in set
Analytic function example
Query information about employees hired before October 10, 2019, including their department, hire date, and other employees in the same department.
obclient> SELECT department_id "Dept",hiredate "Date",last_name "Name",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hiredate, last_name) OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
WHERE hiredate < '2019-10-10'
ORDER BY "Dept", "Date", "Name";
+------+------------+-----------+----------------------------+
| Dept | Date | Name | Emp_list |
+------+------------+-----------+----------------------------+
| 30 | 2017-07-01 | Raphaely | Raphaely; De Haan |
| 30 | 2018-05-01 | De Haan | Raphaely; De Haan |
| 40 | 2017-07-21 | Errazuriz | Errazuriz |
| 50 | 2017-07-22 | Raphaely | Raphaely; Hartstein; Weiss |
| 50 | 2019-10-05 | Hartstein | Raphaely; Hartstein; Weiss |
| 50 | 2019-10-05 | Weiss | Raphaely; Hartstein; Weiss |
| 90 | 2018-12-01 | Partners | Partners; Russell |
| 90 | 2019-07-11 | Russell | Partners; Russell |
+------+------------+-----------+----------------------------+
8 rows in set