All Products
Search
Document Center

LISTAGG

Last Updated: Jun 18, 2021

The LISTAGG function converts columns to rows. LISTAGG sorts the data in each group that is specified in the ORDER BY clause and merges the values of the measure column. When LISTAGG serves as a single-set aggregate function, it performs operations on all the rows and returns a single output row. When LISTAGG serves as a group-set aggregate, it performs operations on each group that is defined by the GROUP BY clause and returns an output row for each group. When LISTAGG serves as an analytic function, it divides the query result set into groups based on one or more expressions in query_partition_clause.

Syntax

LISTAGG(measure_expr [,'delimiter'])WITHIN GROUP (order_by_clause)
[OVER query_partition_clause]

If the function is used as an analytic function, you must use the full syntax of a window function. The function calculates a set of rows and returns multiple values. If the function is used as an aggregate function, the function aggregates a set of rows and returns only one value. In this case, you do not need to add the OVER keyword.

Parameters

Parameter

Description

OVER

Uses the OVER clause to define a window for calculation.

measure_expr

The value can be an expression. Null values in the measure column are ignored.

delimiter

The string that is used to separate the measure values. This clause is optional. Default value: NULL.

Return type

If the data type of the measure column is RAW, the returned data type is RAW. Otherwise, the return value is of the VARCHAR2 type.

Examples

Examples of the analytic function

To create the employees table and insert data into the table, execute the following statements:

CREATE TABLE employees (department_id INT,manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(40, 100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(50, 100, 'Hartstein', '2019-05-01',14000);     
INSERT INTO employees VALUES(50, 100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(70, 100, 'Weiss',  '2019-07-11',13500);     
INSERT INTO employees VALUES(90, 100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(90,100, 'Partners',  '2018-12-01',14000);

Query the employees that were hired before October 10, 2019, the departments and hire dates of the employees, and the other employees in the departments. Execute the following statement:

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";

The following query result is returned:

+------+------------+-----------+---------------------+
| 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 |
|   50 | 2019-05-01 | Hartstein | Raphaely; Hartstein |
|   70 | 2019-07-11 | Weiss     | Weiss               |
|   90 | 2018-12-01 | Partners  | Partners; Russell   |
|   90 | 2019-10-05 | Russell   | Partners; Russell   |
+------+------------+-----------+---------------------+

Examples of the aggregate function

To create the employees table and insert data into the table, execute the following statements:

CREATE TABLE employees (department_id INT,manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(30, 100, 'Errazuriz', '2017-07-01', 1400);
INSERT INTO employees VALUES(30, 100, 'Hartstein', '2019-05-01',14000);     
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'Weiss',  '2019-07-01',13500);     
INSERT INTO employees VALUES(30, 100, 'Russell', '2019-07-01', 13000);
INSERT INTO employees VALUES(30,100, 'Partners',  '2018-12-01',14000);

To query all the employees in the thirtieth department and sort the employees by hire date and last name, execute the following statement:

SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hiredate, last_name)  as "Emp_list", 
MIN(hiredate) as "Earliest" FROM employees WHERE department_id = 30;

The following query result is returned:

+-----------------------------------------------------------------------------+------------+
| Emp_list                                                                    | Earliest   |
+-----------------------------------------------------------------------------+------------+
| Errazuriz; Raphaely; Raphaely; De Haan; Partners; Hartstein; Russell; Weiss | 2017-07-01 |
+-----------------------------------------------------------------------------+------------+