Purpose
You can call this function to concatenate values of a column into one string. LISTAGG sorts data in each group specified in the ORDER BY clause and then concatenates the values of the measure column. You can use it as an aggregate or analytic function.
Note
- When you use it 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 analytic 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.
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 | (Optional) Specifies the string that is used to separate the measure values. 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 about analytic functions, see Analytic functions. |
Return types
If the measure column is RAW, this function returns data of the RAW type. Otherwise, it returns data of the VARCHAR2 type.
Examples
Assume that you have created the table employees.
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 example
Execute the following statement to 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
Analytic example
The following example queries information 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