Purpose
This function is used to convert rows to columns. The LISTAGG function sorts the data in each group specified by the ORDER BY clause and then concatenates the values of the measure column. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for calculation. 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 and defaults to NULL. |
| OVER | Use the OVER clause to define the window for calculation. 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
An existing table named employees 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 last name.
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 employee information hired before October 10, 2019, including the employee's department, hire date, and other employees in the 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