LISTAGG

Syntax

Purpose

For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.

• As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

• As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.

• As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.

The arguments to the function are subject to the following rules:

• The measure_expr can be any expression. Null values in the measure column are ignored.

• The delimiter_expr designates the string that is to separate the measure values. This clause is optional and defaults to NULL.

• The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.

The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2.

Aggregate Examples

The following single-set aggregate example lists all of the employees in Department 30 in the hr.employees table, ordered by hire date and last name:

SELECT LISTAGG(last_name, '; ')
 WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
 MIN(hire_date) "Earliest"
 FROM employees
 WHERE department_id = 30;
Emp_list Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 07-DEC-02

The following group-set aggregate example lists, for each department ID in the hr.employees table, the employees in that department in order of their hire date:

SELECT department_id "Dept.",
 LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"
 FROM employees
 GROUP BY department_id
 ORDER BY department_id;
Dept. Employees
------ ------------------------------------------------------------
 10 Whalen
 20 Hartstein; Fay
 30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
 40 Mavris
 50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
 s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
 ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
 el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
 Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
 an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
 60 Austin; Hunold; Pataballa; Lorentz; Ernst
 70 Baer
. . .

Analytic Example

The following analytic example shows, for each employee hired earlier than September 1, 2003, the employee's department, hire date, and all other employees in that department also hired before September 1, 2003:

SELECT department_id "Dept", hire_date "Date", last_name "Name",
 LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
 OVER (PARTITION BY department_id) as "Emp_list"
 FROM employees
 WHERE hire_date < '01-SEP-2003'
 ORDER BY "Dept", "Date", "Name";
 Dept Date Name Emp_list
----- --------- --------------- ---------------------------------------------
 30 07-DEC-02 Raphaely Raphaely; Khoo
 30 18-MAY-03 Khoo Raphaely; Khoo
 40 07-JUN-02 Mavris Mavris
 50 01-MAY-03 Kaufling Kaufling; Ladwig
 50 14-JUL-03 Ladwig Kaufling; Ladwig
 70 07-JUN-02 Baer Baer
 90 13-JAN-01 De Haan De Haan; King
 90 17-JUN-03 King De Haan; King
 100 16-AUG-02 Faviet Faviet; Greenberg
 100 17-AUG-02 Greenberg Faviet; Greenberg
 110 07-JUN-02 Gietz Gietz; Higgins
 110 07-JUN-02 Higgins Gietz; Higgins