Purpose
LISTAGG() concatenates values of a column into one string. It sorts data in each group specified in the ORDER BY clause and then concatenates the values of the measure column. You can use this function as an aggregate or analytic function.
Note
- When you use this function as an analytic function, use the
OVERclause to define a window over the data on which the function operates. This function divides the query result set into groups based on one or more expressions inquery_partition_clause. - When you use it as an aggregate function, you do not need to add the
OVERkeyword.- As a single-set aggregate function, the
LISTAGGfunction operates on all rows and returns a single output row. - As a group-set aggregate function, the
LISTAGGfunction operates on each group specified in theGROUP BYclause and returns an output row.
- As a single-set aggregate function, the
Syntax
LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)
[ OVER query_partition_clause ]
Parameters
Parameter |
Description |
|---|---|
| measure_expr | An expression of any type. NULLs in the measure column are ignored. |
| delimiter | The string that is used to separate the measure values. This parameter is optional. Default value: NULL. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
If the measure column is RAW, this function returns data of the RAW type. Otherwise, it returns data of the VARCHAR2 type.
Examples
The table 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
Example of an aggregate function
Query all the employees in the 30th department and sort the employees by their 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
Example of an analytic function
Query the department, hire date, and department employee list for employees hired before October 10, 2019.
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