Oracle Live SQL - Script: How to combine rows into CSV strings using LISTAGG

Statement 1

By default, LISTAGG is an aggregate. This combines all the rows in each subset into one. So this produces a CSV of names in each department. The WITHIN GROUP controls which order the values appear in the list. If you omit this the values could be returned in any order.

Sorting the list

select    
  department_id d_id,    
  listagg ( last_name, ',' ) names_random_order,    
  listagg ( last_name, ',' )   
    within group ( order by employee_id ) names_by_id,    
  listagg ( last_name, ',' )   
    within group ( order by last_name ) names_by_name   
from   hr.employees   
where  department_id in ( 20, 30, 60 )   
group  by department_id


D_ID NAMES_RANDOM_ORDER NAMES_BY_ID NAMES_BY_NAME
20 Hartstein,Fay Hartstein,Fay Fay,Hartstein
30 Raphaely,Colmenares,Himuro,Tobias,Baida,Khoo Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
60 Hunold,Lorentz,Pataballa,Austin,Ernst Hunold,Ernst,Austin,Pataballa,Lorentz Austin,Ernst,Hunold,Lorentz,Pataballa

3 rows selected.

D_ID	NAMES_RANDOM_ORDER	NAMES_BY_ID	NAMES_BY_NAME
20	Hartstein,Fay	Hartstein,Fay	Fay,Hartstein
30	Raphaely,Colmenares,Himuro,Tobias,Baida,Khoo	Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
60	Hunold,Lorentz,Pataballa,Austin,Ernst	Hunold,Ernst,Austin,Pataballa,Lorentz	Austin,Ernst,Hunold,Lorentz,Pataballa

Statement 2

You can use LISTAGG to combine expressions. For example, to enclose the values in quotes.

Concatenating expressions

select   
  department_id,   
  listagg ( '"' || last_name || '"', ',' ) names_random_order  
from   hr.employees  
where  department_id in ( 20, 30, 60 )  
group  by department_id


DEPARTMENT_ID NAMES_RANDOM_ORDER
20 "Hartstein","Fay"
30 "Raphaely","Colmenares","Himuro","Tobias","Baida","Khoo"
60 "Hunold","Lorentz","Pataballa","Austin","Ernst"

3 rows selected.

DEPARTMENT_ID	NAMES_RANDOM_ORDER
20	"Hartstein","Fay"
30	"Raphaely","Colmenares","Himuro","Tobias","Baida","Khoo"
60	"Hunold","Lorentz","Pataballa","Austin","Ernst"

Statement 3

Adding the OVER clause turns LISTAGG into an analytic. This returns all the input rows with the list of values for these.

LISTAGG analytic

select   
  department_id, last_name,  
  listagg ( last_name, ',' )  
    over ( partition by department_id ) dept_names,   
  listagg ( last_name, ',' )  
    over () all_names 
from   hr.employees  
where  department_id in ( 20, 30, 60 )


DEPARTMENT_ID LAST_NAME DEPT_NAMES ALL_NAMES
20 Hartstein Hartstein,Fay Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
20 Fay Hartstein,Fay Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30 Raphaely Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30 Khoo Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30 Baida Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30 Tobias Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30 Himuro Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30 Colmenares Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60 Hunold Hunold,Ernst,Austin,Pataballa,Lorentz Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60 Ernst Hunold,Ernst,Austin,Pataballa,Lorentz Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60 Austin Hunold,Ernst,Austin,Pataballa,Lorentz Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60 Pataballa Hunold,Ernst,Austin,Pataballa,Lorentz Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60 Lorentz Hunold,Ernst,Austin,Pataballa,Lorentz Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz

13 rows selected.

DEPARTMENT_ID	LAST_NAME	DEPT_NAMES	ALL_NAMES
20	Hartstein	Hartstein,Fay	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
20	Fay	Hartstein,Fay	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30	Raphaely	Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30	Khoo	Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30	Baida	Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30	Tobias	Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30	Himuro	Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
30	Colmenares	Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60	Hunold	Hunold,Ernst,Austin,Pataballa,Lorentz	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60	Ernst	Hunold,Ernst,Austin,Pataballa,Lorentz	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60	Austin	Hunold,Ernst,Austin,Pataballa,Lorentz	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60	Pataballa	Hunold,Ernst,Austin,Pataballa,Lorentz	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz
60	Lorentz	Hunold,Ernst,Austin,Pataballa,Lorentz	Hartstein,Fay,Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares,Hunold,Ernst,Austin,Pataballa,Lorentz

Statement 4

Here LISTAGG is a window function, not an aggregate. It's input (LAST_NAME) is not in the GROUP BY or an aggregate function, so this statement fails.

LISTAGG grouping and windowing

select    
  department_id,    
  listagg ( last_name, ',' )   
    over () all_names_by_name   
from   hr.employees   
group  by department_id

ORA-00979: not a GROUP BY expression

More Details: https://docs.oracle.com/error-help/db/ora-00979

Statement 5

Splitting statement 4 into a subquery makes the problem clearer. LAST_NAME is in the subquery's SELECT list without an aggregate, but missing from the GROUP BY. So the query fails.

LISTAGG grouping and windowing

with rws as (   
  select    
    department_id,    
    last_name   
  from   hr.employees   
  group  by department_id   
)   
  select    
    department_id,    
    listagg ( last_name, ',' )   
      over () all_names_by_name   
  from   rws

ORA-00979: not a GROUP BY expression

More Details: https://docs.oracle.com/error-help/db/ora-00979

Statement 6

This is the same problem as above using MIN instead of LISTAGG.

Grouping and windowing

select min ( last_name ) over ()   
from   hr.employees   
group  by department_id

ORA-00979: not a GROUP BY expression

More Details: https://docs.oracle.com/error-help/db/ora-00979

Statement 7

You can use LISTAGG as an analytic when the query has GROUP BY. But the columns in LISTAGG's expression must all be in the GROUP BY...

LISTAGG grouping and windowing

select   
  department_id, last_name,  
  listagg ( last_name, ',' )  
    over ( partition by department_id ) dept_names  
from   hr.employees  
where  department_id in ( 20, 30, 60 )  
group  by department_id, last_name


DEPARTMENT_ID LAST_NAME DEPT_NAMES
20 Fay Fay,Hartstein
20 Hartstein Fay,Hartstein
30 Baida Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Colmenares Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Himuro Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Khoo Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Raphaely Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Tobias Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
60 Austin Austin,Ernst,Hunold,Lorentz,Pataballa
60 Ernst Austin,Ernst,Hunold,Lorentz,Pataballa
60 Hunold Austin,Ernst,Hunold,Lorentz,Pataballa
60 Lorentz Austin,Ernst,Hunold,Lorentz,Pataballa
60 Pataballa Austin,Ernst,Hunold,Lorentz,Pataballa

13 rows selected.

DEPARTMENT_ID	LAST_NAME	DEPT_NAMES
20	Fay	Fay,Hartstein
20	Hartstein	Fay,Hartstein
30	Baida	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Colmenares	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Himuro	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Khoo	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Raphaely	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Tobias	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
60	Austin	Austin,Ernst,Hunold,Lorentz,Pataballa
60	Ernst	Austin,Ernst,Hunold,Lorentz,Pataballa
60	Hunold	Austin,Ernst,Hunold,Lorentz,Pataballa
60	Lorentz	Austin,Ernst,Hunold,Lorentz,Pataballa
60	Pataballa	Austin,Ernst,Hunold,Lorentz,Pataballa

Statement 8

...or you can nest an aggregate calling LAST_NAME in the windowed version of LISTAGG.

LISTAGG grouping and windowing

select   
  department_id,   
  -- List of lowest surname in each department  
  listagg ( min ( last_name ), ',' )  
    over () all_lowest_names  
from   hr.employees  
where  department_id in ( 20, 30, 60 )  
group  by department_id


DEPARTMENT_ID ALL_LOWEST_NAMES
20 Fay,Baida,Austin
30 Fay,Baida,Austin
60 Fay,Baida,Austin

3 rows selected.

DEPARTMENT_ID	ALL_LOWEST_NAMES
20	Fay,Baida,Austin
30	Fay,Baida,Austin
60	Fay,Baida,Austin

Statement 9

Splitting the aggregate in the previous statement into a subquery makes its intent clearer. First it finds the lowest name in each department. Then combines these lowest names into a list across all the departments, while still listing a row for each department.

LISTAGG grouping and windowing

with dept_lowest_names as (  
  select   
    department_id,   
    min ( last_name ) lowest_last_name  
  from   hr.employees  
  where  department_id in ( 20, 30, 60 )  
  group  by department_id  
)  
select   
  department_id,   
  listagg ( lowest_last_name, ',' )  
    over () all_lowest_names  
from   dept_lowest_names


DEPARTMENT_ID ALL_LOWEST_NAMES
20 Fay,Baida,Austin
30 Fay,Baida,Austin
60 Fay,Baida,Austin

3 rows selected.

DEPARTMENT_ID	ALL_LOWEST_NAMES
20	Fay,Baida,Austin
30	Fay,Baida,Austin
60	Fay,Baida,Austin

Statement 10

This uses LISTAGG as both an aggregate and analytic in the same query. This can be difficult to understand. This query returns a row for every LAST_NAME in each DEPARTMENT_ID. It combines rows with the same LAST_NAME into one.

LISTAGG grouping and windowing

select   
  department_id, last_name,  
  -- Number of people with same last name in each department  
  count (*),  
  -- List the first names of everyone with the same last name in a dept  
  listagg ( first_name, ',' ) aggregate_first_names,  
  -- List all the unique last names in the department  
  listagg ( last_name, ',' )  
    over ( partition by department_id ) window_last_names  
from   hr.employees  
group  by department_id, last_name  
order  by count (*) desc  
fetch  first 10 rows only


DEPARTMENT_ID LAST_NAME COUNT(*) AGGREGATE_FIRST_NAMES WINDOW_LAST_NAMES
80 Cambrault 2 Gerald,Nanette Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Johnson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olsen,Ozer,Partners,Russell,Sewall,Smith,Sully,Taylor,Tucker,Tuvault,Vishney,Zlotkey
80 Smith 2 Lindsey,William Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Johnson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olsen,Ozer,Partners,Russell,Sewall,Smith,Sully,Taylor,Tucker,Tuvault,Vishney,Zlotkey
10 Whalen 1 Jennifer Whalen
30 Baida 1 Shelli Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Colmenares 1 Karen Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Himuro 1 Guy Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Khoo 1 Alexander Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Raphaely 1 Den Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Tobias 1 Sigal Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
40 Mavris 1 Susan Mavris

10 rows selected.

DEPARTMENT_ID	LAST_NAME	COUNT(*)	AGGREGATE_FIRST_NAMES	WINDOW_LAST_NAMES
80	Cambrault	2	Gerald,Nanette	Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Johnson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olsen,Ozer,Partners,Russell,Sewall,Smith,Sully,Taylor,Tucker,Tuvault,Vishney,Zlotkey
80	Smith	2	Lindsey,William	Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Johnson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olsen,Ozer,Partners,Russell,Sewall,Smith,Sully,Taylor,Tucker,Tuvault,Vishney,Zlotkey
10	Whalen	1	Jennifer	Whalen
30	Baida	1	Shelli	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Colmenares	1	Karen	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Himuro	1	Guy	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Khoo	1	Alexander	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Raphaely	1	Den	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Tobias	1	Sigal	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
40	Mavris	1	Susan	Mavris

Statement 11

Note that changing the HAVING filter in the previous query affects the windowed version of LISTAGG, but not the aggregate.

LISTAGG grouping and windowing

select   
  department_id, last_name,  
  -- Number of people with same last name in each department  
  count (*),  
  -- List the first names of everyone with the same last name in a dept  
  listagg ( first_name, ',' ) aggregate_first_names,  
  -- List all the unique last names in the department  
  listagg ( last_name, ',' )  
    over ( partition by department_id ) window_last_names  
from   hr.employees  
group  by department_id, last_name  
having count (*) > 1   
order  by count (*) desc  
fetch  first 10 rows only


DEPARTMENT_ID LAST_NAME COUNT(*) AGGREGATE_FIRST_NAMES WINDOW_LAST_NAMES
80 Cambrault 2 Gerald,Nanette Cambrault,Smith
80 Smith 2 Lindsey,William Cambrault,Smith

2 rows selected.

DEPARTMENT_ID	LAST_NAME	COUNT(*)	AGGREGATE_FIRST_NAMES	WINDOW_LAST_NAMES
80	Cambrault	2	Gerald,Nanette	Cambrault,Smith
80	Smith	2	Lindsey,William	Cambrault,Smith

Statement 12

Again, using a subquery can make it easier to understand what's happening.

LISTAGG grouping and windowing

with last_name_stats_per_dept as (  
  select   
    department_id,   
    last_name,  
    count (*) c,  
    listagg ( first_name, ',' ) first_names  
  from   hr.employees  
  group  by department_id, last_name  
)  
  select   
    department_id, last_name,  
    c, first_names,  
    listagg ( last_name, ',' )  
      over ( partition by department_id ) unique_dept_last_names  
  from   last_name_stats_per_dept  
  order  by c desc  
  fetch  first 10 rows only


DEPARTMENT_ID LAST_NAME C FIRST_NAMES UNIQUE_DEPT_LAST_NAMES
80 Cambrault 2 Gerald,Nanette Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Johnson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olsen,Ozer,Partners,Russell,Sewall,Smith,Sully,Taylor,Tucker,Tuvault,Vishney,Zlotkey
80 Smith 2 Lindsey,William Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Johnson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olsen,Ozer,Partners,Russell,Sewall,Smith,Sully,Taylor,Tucker,Tuvault,Vishney,Zlotkey
10 Whalen 1 Jennifer Whalen
30 Baida 1 Shelli Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Colmenares 1 Karen Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Himuro 1 Guy Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Khoo 1 Alexander Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Raphaely 1 Den Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30 Tobias 1 Sigal Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
40 Mavris 1 Susan Mavris

10 rows selected.

DEPARTMENT_ID	LAST_NAME	C	FIRST_NAMES	UNIQUE_DEPT_LAST_NAMES
80	Cambrault	2	Gerald,Nanette	Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Johnson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olsen,Ozer,Partners,Russell,Sewall,Smith,Sully,Taylor,Tucker,Tuvault,Vishney,Zlotkey
80	Smith	2	Lindsey,William	Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Johnson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olsen,Ozer,Partners,Russell,Sewall,Smith,Sully,Taylor,Tucker,Tuvault,Vishney,Zlotkey
10	Whalen	1	Jennifer	Whalen
30	Baida	1	Shelli	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Colmenares	1	Karen	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Himuro	1	Guy	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Khoo	1	Alexander	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Raphaely	1	Den	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
30	Tobias	1	Sigal	Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
40	Mavris	1	Susan	Mavris

Statement 13

You can't use ORDER BY in the OVER clause of LISTAGG.

No sliding windows for LISTAGG

select   
  department_id, last_name,  
  listagg ( last_name, ',' )  
    over ( order by department_id ) all_names_by_dept_id  
from   hr.employees  
where  department_id in ( 20, 30, 60 )

ORA-30487: ORDER BY not allowed here

More Details: https://docs.oracle.com/error-help/db/ora-30487

Statement 14

To create a "running total" CSV that adds each row to the end, use a hierarchical query.

CSV running total

with rws as (   
  select e.*,    
         row_number() over (    
           partition by department_id    
           order by employee_id    
         ) rn   
  from   hr.employees e   
  where  department_id = 30   
), running_names (    
  employee_id, department_id, rn, last_name, name_list    
) as (   
  select employee_id, department_id, rn, last_name, last_name 
  from   rws r   
  where  rn = 1   
  union all   
  select r.employee_id, r.department_id, r.rn, r.last_name,    
         rn.name_list || ',' || r.last_name 
  from   rws r   
  join   running_names rn   
  on     r.department_id = rn.department_id   
  and    r.rn - 1 = rn.rn   
)    
  select * from running_names


EMPLOYEE_ID DEPARTMENT_ID RN LAST_NAME NAME_LIST
114 30 1 Raphaely Raphaely
115 30 2 Khoo Raphaely,Khoo
116 30 3 Baida Raphaely,Khoo,Baida
117 30 4 Tobias Raphaely,Khoo,Baida,Tobias
118 30 5 Himuro Raphaely,Khoo,Baida,Tobias,Himuro
119 30 6 Colmenares Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares

6 rows selected.

EMPLOYEE_ID	DEPARTMENT_ID	RN	LAST_NAME	NAME_LIST
114	30	1	Raphaely	Raphaely
115	30	2	Khoo	Raphaely,Khoo
116	30	3	Baida	Raphaely,Khoo,Baida
117	30	4	Tobias	Raphaely,Khoo,Baida,Tobias
118	30	5	Himuro	Raphaely,Khoo,Baida,Tobias,Himuro
119	30	6	Colmenares	Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares

Statement 15

You can use the MODEL clause to create a "sliding CSV" of the last N values.

CSV sliding window

select *   
from   hr.employees  
where  department_id = 30  
model   
  dimension by (   
    row_number () over ( order by employee_id ) rn   
  )  
  measures (   
    department_id, employee_id, last_name,  
    cast ( ' ' as varchar2(100 ) ) strlist  
  ) (  
    strlist[any] =   
      last_name[cv()] ||   
      case when last_name[cv()-1] is not null then  
        ',' || last_name[cv()-1]   
      end  ||   
      case when last_name[cv()-2] is not null then  
        ',' || last_name[cv()-2]   
      end   
  )


RN DEPARTMENT_ID EMPLOYEE_ID LAST_NAME STRLIST
1 30 114 Raphaely Raphaely
2 30 115 Khoo Khoo,Raphaely
3 30 116 Baida Baida,Khoo,Raphaely
4 30 117 Tobias Tobias,Baida,Khoo
5 30 118 Himuro Himuro,Tobias,Baida
6 30 119 Colmenares Colmenares,Himuro,Tobias

6 rows selected.

RN	DEPARTMENT_ID	EMPLOYEE_ID	LAST_NAME	STRLIST
1	30	114	Raphaely	Raphaely
2	30	115	Khoo	Khoo,Raphaely
3	30	116	Baida	Baida,Khoo,Raphaely
4	30	117	Tobias	Tobias,Baida,Khoo
5	30	118	Himuro	Himuro,Tobias,Baida
6	30	119	Colmenares	Colmenares,Himuro,Tobias

Statement 16

LISTAGG returns a VARCHAR2. If the final string is longer than this limit, the query will raise ORA-1489.

LISTAGG limit

select   
  owner,   
  listagg ( object_name )   
    within group ( order by object_name )  
from   all_objects  
group  by owner

ORA-01489: result of string concatenation is too long

More Details: https://docs.oracle.com/error-help/db/ora-01489

Statement 17

The ON OVERFLOW TRUNCATE clause trims the final string to the max VARCHAR2 length.

Avoiding ORA-1489 - string truncation

with rws as (  
  select   
    owner,   
    listagg (   
      object_name, ', '   
      on overflow truncate   
    ) within group ( order by object_name ) default_trunc,   
    listagg (   
      object_name, ', '   
      on overflow truncate   
        'more' without count  
    ) within group ( order by object_name ) no_count_trunc  
  from   all_objects  
  group  by owner  
)  
  select owner,   
         substr ( default_trunc, -20, 20 ) default_trunc,   
         substr ( no_count_trunc, -20, 20 ) no_count_trunc  
  from   rws  
  where  no_count_trunc like '%more'


OWNER DEFAULT_TRUNC NO_COUNT_TRUNC
MDSYS ryOpImage, ...(1881) rBinaryOpImage, more
PUBLIC _MEAS_AE, ...(10484) VIEW_CALC_MEAS, more
SYS STRAINTS, ...(36009) RUNNING_CHAINS, more

3 rows selected.

OWNER	DEFAULT_TRUNC	NO_COUNT_TRUNC
MDSYS	ryOpImage, ...(1881)	rBinaryOpImage, more
PUBLIC	_MEAS_AE, ...(10484)	VIEW_CALC_MEAS, more
SYS	STRAINTS, ...(36009)	RUNNING_CHAINS, more

Statement 18

To avoid ORA-1489 you can split the text over many rows using pattern matching. This limits the line length to 80 bytes. This can be useful if you need to write fixed-width files.

Avoiding ORA-1489 - splitting strings

with rws as (  
  select owner, object_name, grp  
  from   all_objects   
    match_recognize (  
      partition by owner  
      order by object_name  
      measures  
        match_number() as grp  
      all rows per match  
      pattern ( init len* )  
      define   
        len as lengthb ( init.object_name )   
          + sum ( lengthb ( len.object_name ) + lengthb ( ', ') ) <= 80  
    )  
  where  owner in ( 'HR', 'SH', 'CO' )  
)  
  select   
    owner, grp,  
    listagg ( object_name, ', ' )  
  from   rws  
  group  by owner, grp


OWNER GRP LISTAGG(OBJECT_NAME,',')
CO 1 CUSTOMERS, CUSTOMERS_PK, CUSTOMERS_NAME_I, CUSTOMERS_EMAIL_U
CO 2 CUSTOMER_ORDER_PRODUCTS, ORDERS_PK, ORDERS_CUSTOMER_ID_I, ORDERS
CO 3 ORDERS_STORE_ID_I, PRODUCTS, ORDER_ITEMS_PRODUCT_U, ORDER_ITEMS_PK, ORDER_ITEMS
CO 4 PRODUCTS_PK, STORE_NAME_U, STORES_PK, STORES, PRODUCT_REVIEWS, PRODUCT_ORDERS
CO 5 STORE_ORDERS
HR 1 COUNTRIES, EMPLOYEES, DEPT_LOCATION_IX, DEPT_ID_PK, DEPARTMENTS, COUNTRY_C_ID_PK
HR 2 EMP_DEPARTMENT_IX, EMP_JOB_IX, EMP_EMP_ID_PK, EMP_EMAIL_UK, EMP_DETAILS_VIEW
HR 3 EMP_MANAGER_IX, JHIST_EMPLOYEE_IX, JHIST_DEPARTMENT_IX, EMP_NAME_IX
HR 4 JHIST_EMP_ID_ST_DATE_PK, LOCATIONS, JOB_ID_PK, JOB_HISTORY, JOBS, JHIST_JOB_IX
HR 5 LOC_CITY_IX, REGIONS, LOC_STATE_PROVINCE_IX, LOC_ID_PK, LOC_COUNTRY_IX
HR 6 REG_ID_PK
SH 1 CAL_MONTH_SALES_MV, COSTS, COSTS, COSTS, COSTS, COSTS, CHANNELS_PK, CHANNELS
SH 2 COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS
SH 3 COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS
SH 4 COSTS, CUSTOMERS, COUNTRIES_PK, COUNTRIES, COSTS_TIME_BIX, COSTS_PROD_BIX, COSTS
SH 5 CUSTOMERS_GENDER_BIX, CUSTOMERS_YOB_BIX, CUSTOMERS_PK, CUSTOMERS_MARITAL_BIX
SH 6 DIMENSION_EXCEPTIONS, DR$SUP_TEXT_IDX$KD, DR$SUP_TEXT_IDX$K, DR$SUP_TEXT_IDX$I
SH 7 DR$SUP_TEXT_IDX$KR, DR$SUP_TEXT_IDX$X, DR$SUP_TEXT_IDX$U, DR$SUP_TEXT_IDX$N
SH 8 FWEEK_PSCAT_SALES_MV, FW_PSC_S_MV_PROMO_BIX, FW_PSC_S_MV_CHAN_BIX
SH 9 FW_PSC_S_MV_SUBCAT_BIX, PRODUCTS_PK, PRODUCTS, FW_PSC_S_MV_WD_BIX
SH 10 PRODUCTS_PROD_CAT_IX, PROFITS, PRODUCTS_PROD_SUBCAT_IX, PRODUCTS_PROD_STATUS_BIX
SH 11 PROMOTIONS, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, PROMO_PK
SH 12 SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES
SH 13 SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES
SH 14 SALES_CHANNEL_BIX, SALES_PROMO_BIX, SALES_PROD_BIX, SALES_CUST_BIX
SH 15 SALES_TIME_BIX, SYS_C0011638, SUP_TEXT_IDX, SUPPLEMENTARY_DEMOGRAPHICS
SH 16 SYS_IOT_TOP_79606, TIMES_PK, TIMES

27 rows selected.

OWNER	GRP	LISTAGG(OBJECT_NAME,',')
CO	1	CUSTOMERS, CUSTOMERS_PK, CUSTOMERS_NAME_I, CUSTOMERS_EMAIL_U
CO	2	CUSTOMER_ORDER_PRODUCTS, ORDERS_PK, ORDERS_CUSTOMER_ID_I, ORDERS
CO	3	ORDERS_STORE_ID_I, PRODUCTS, ORDER_ITEMS_PRODUCT_U, ORDER_ITEMS_PK, ORDER_ITEMS
CO	4	PRODUCTS_PK, STORE_NAME_U, STORES_PK, STORES, PRODUCT_REVIEWS, PRODUCT_ORDERS
CO	5	STORE_ORDERS
HR	1	COUNTRIES, EMPLOYEES, DEPT_LOCATION_IX, DEPT_ID_PK, DEPARTMENTS, COUNTRY_C_ID_PK
HR	2	EMP_DEPARTMENT_IX, EMP_JOB_IX, EMP_EMP_ID_PK, EMP_EMAIL_UK, EMP_DETAILS_VIEW
HR	3	EMP_MANAGER_IX, JHIST_EMPLOYEE_IX, JHIST_DEPARTMENT_IX, EMP_NAME_IX
HR	4	JHIST_EMP_ID_ST_DATE_PK, LOCATIONS, JOB_ID_PK, JOB_HISTORY, JOBS, JHIST_JOB_IX
HR	5	LOC_CITY_IX, REGIONS, LOC_STATE_PROVINCE_IX, LOC_ID_PK, LOC_COUNTRY_IX
HR	6	REG_ID_PK
SH	1	CAL_MONTH_SALES_MV, COSTS, COSTS, COSTS, COSTS, COSTS, CHANNELS_PK, CHANNELS
SH	2	COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS
SH	3	COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS
SH	4	COSTS, CUSTOMERS, COUNTRIES_PK, COUNTRIES, COSTS_TIME_BIX, COSTS_PROD_BIX, COSTS
SH	5	CUSTOMERS_GENDER_BIX, CUSTOMERS_YOB_BIX, CUSTOMERS_PK, CUSTOMERS_MARITAL_BIX
SH	6	DIMENSION_EXCEPTIONS, DR$SUP_TEXT_IDX$KD, DR$SUP_TEXT_IDX$K, DR$SUP_TEXT_IDX$I
SH	7	DR$SUP_TEXT_IDX$KR, DR$SUP_TEXT_IDX$X, DR$SUP_TEXT_IDX$U, DR$SUP_TEXT_IDX$N
SH	8	FWEEK_PSCAT_SALES_MV, FW_PSC_S_MV_PROMO_BIX, FW_PSC_S_MV_CHAN_BIX
SH	9	FW_PSC_S_MV_SUBCAT_BIX, PRODUCTS_PK, PRODUCTS, FW_PSC_S_MV_WD_BIX
SH	10	PRODUCTS_PROD_CAT_IX, PROFITS, PRODUCTS_PROD_SUBCAT_IX, PRODUCTS_PROD_STATUS_BIX
SH	11	PROMOTIONS, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, PROMO_PK
SH	12	SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES
SH	13	SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES
SH	14	SALES_CHANNEL_BIX, SALES_PROMO_BIX, SALES_PROD_BIX, SALES_CUST_BIX
SH	15	SALES_TIME_BIX, SYS_C0011638, SUP_TEXT_IDX, SUPPLEMENTARY_DEMOGRAPHICS
SH	16	SYS_IOT_TOP_79606, TIMES_PK, TIMES

Statement 19

You can combine rows into a CSV CLOB by first generating XML, then extracting the values. This can be slow on large data sets.

Avoiding ORA-1489 - XML

select  
  owner,   
  xmlagg (  
    xmlelement ( e, object_name, ', ' ).extract ('//text()')  
      order by object_name  
  ).getclobval () name_csv  
from   all_objects  
where  owner = 'SH'  
group by owner


OWNER NAME_CSV
SH CAL_MONTH_SALES_MV, CHANNELS, CHANNELS_PK, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS_PROD_BIX, COSTS_TIME_BIX, COUNTRIES, COUNTRIES_PK, CUSTOMERS, CUSTOMERS_GENDER_BIX, CUSTOMERS_MARITAL_BIX, CUSTOMERS_PK, CUSTOMERS_YOB_BIX, DIMENSION_EXCEPTIONS, DR$SUP_TEXT_IDX$I, DR$SUP_TEXT_IDX$K, DR$SUP_TEXT_IDX$KD, DR$SUP_TEXT_IDX$KR, DR$SUP_TEXT_IDX$N, DR$SUP_TEXT_IDX$U, DR$SUP_TEXT_IDX$X, FWEEK_PSCAT_SALES_MV, FW_PSC_S_MV_CHAN_BIX, FW_PSC_S_MV_PROMO_BIX, FW_PSC_S_MV_SUBCAT_BIX, FW_PSC_S_MV_WD_BIX, PRODUCTS, PRODUCTS_PK, PRODUCTS_PROD_CAT_IX, PRODUCTS_PROD_STATUS_BIX, PRODUCTS_PROD_SUBCAT_IX, PROFITS, PROMOTIONS, PROMO_PK, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES_CHANNEL_BIX, SALES_CUST_BIX, SALES_PROD_BIX, SALES_PROMO_BIX, SALES_TIME_BIX, SUPPLEMENTARY_DEMOGRAPHICS, SUP_TEXT_IDX, SYS_C0011638, SYS_IOT_TOP_79606, TIMES, TIMES_PK,

OWNER	NAME_CSV
SH	CAL_MONTH_SALES_MV, CHANNELS, CHANNELS_PK, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS, COSTS_PROD_BIX, COSTS_TIME_BIX, COUNTRIES, COUNTRIES_PK, CUSTOMERS, CUSTOMERS_GENDER_BIX, CUSTOMERS_MARITAL_BIX, CUSTOMERS_PK, CUSTOMERS_YOB_BIX, DIMENSION_EXCEPTIONS, DR$SUP_TEXT_IDX$I, DR$SUP_TEXT_IDX$K, DR$SUP_TEXT_IDX$KD, DR$SUP_TEXT_IDX$KR, DR$SUP_TEXT_IDX$N, DR$SUP_TEXT_IDX$U, DR$SUP_TEXT_IDX$X, FWEEK_PSCAT_SALES_MV, FW_PSC_S_MV_CHAN_BIX, FW_PSC_S_MV_PROMO_BIX, FW_PSC_S_MV_SUBCAT_BIX, FW_PSC_S_MV_WD_BIX, PRODUCTS, PRODUCTS_PK, PRODUCTS_PROD_CAT_IX, PRODUCTS_PROD_STATUS_BIX, PRODUCTS_PROD_SUBCAT_IX, PROFITS, PROMOTIONS, PROMO_PK, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES, SALES_CHANNEL_BIX, SALES_CUST_BIX, SALES_PROD_BIX, SALES_PROMO_BIX, SALES_TIME_BIX, SUPPLEMENTARY_DEMOGRAPHICS, SUP_TEXT_IDX, SYS_C0011638, SYS_IOT_TOP_79606, TIMES, TIMES_PK,

Statement 20

Or (from 18c) you can convert the data to a JSON array to return a "CSV CLOB". Remove the first and last characters to make this a plain CSV instead of JSON.

Avoiding ORA-1489 - JSON

select   
  owner,    
  substr (    
    json_arrayagg (    
      object_name   
      order by object_name    
      returning clob    
    ), 1, 1000   
  ) type_array   
from   all_objects   
where  owner in ( 'SH' )   
group by owner


OWNER TYPE_ARRAY
SH ["CAL_MONTH_SALES_MV","CHANNELS","CHANNELS_PK","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS_PROD_BIX","COSTS_TIME_BIX","COUNTRIES","COUNTRIES_PK","CUSTOMERS","CUSTOMERS_GENDER_BIX","CUSTOMERS_MARITAL_BIX","CUSTOMERS_PK","CUSTOMERS_YOB_BIX","DIMENSION_EXCEPTIONS","DR$SUP_TEXT_IDX$I","DR$SUP_TEXT_IDX$K","DR$SUP_TEXT_IDX$KD","DR$SUP_TEXT_IDX$KR","DR$SUP_TEXT_IDX$N","DR$SUP_TEXT_IDX$U","DR$SUP_TEXT_IDX$X","FWEEK_PSCAT_SALES_MV","FW_PSC_S_MV_CHAN_BIX","FW_PSC_S_MV_PROMO_BIX","FW_PSC_S_MV_SUBCAT_BIX","FW_PSC_S_MV_WD_BIX","PRODUCTS","PRODUCTS_PK","PRODUCTS_PROD_CAT_IX","PRODUCTS_PROD_STATUS_BIX","PRODUCTS_PROD_SUBCAT_IX","PROFITS","PROMOTIONS","PROMO_PK","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES

OWNER	TYPE_ARRAY
SH	["CAL_MONTH_SALES_MV","CHANNELS","CHANNELS_PK","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS","COSTS_PROD_BIX","COSTS_TIME_BIX","COUNTRIES","COUNTRIES_PK","CUSTOMERS","CUSTOMERS_GENDER_BIX","CUSTOMERS_MARITAL_BIX","CUSTOMERS_PK","CUSTOMERS_YOB_BIX","DIMENSION_EXCEPTIONS","DR$SUP_TEXT_IDX$I","DR$SUP_TEXT_IDX$K","DR$SUP_TEXT_IDX$KD","DR$SUP_TEXT_IDX$KR","DR$SUP_TEXT_IDX$N","DR$SUP_TEXT_IDX$U","DR$SUP_TEXT_IDX$X","FWEEK_PSCAT_SALES_MV","FW_PSC_S_MV_CHAN_BIX","FW_PSC_S_MV_PROMO_BIX","FW_PSC_S_MV_SUBCAT_BIX","FW_PSC_S_MV_WD_BIX","PRODUCTS","PRODUCTS_PK","PRODUCTS_PROD_CAT_IX","PRODUCTS_PROD_STATUS_BIX","PRODUCTS_PROD_SUBCAT_IX","PROFITS","PROMOTIONS","PROMO_PK","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES","SALES

Statement 21

Oracle Database 19c added the DISTINCT option, which removes duplicates from the list.

De-duplicating values in the list

select    
  owner,    
  listagg ( distinct object_type, ', ' )    
    within group ( order by object_type ) object_types   
from   all_objects   
where  owner in ( 'HR', 'PUBLIC', 'SH' )   
group  by owner


OWNER OBJECT_TYPES
HR INDEX, TABLE, VIEW
PUBLIC SYNONYM
SH INDEX, TABLE, TABLE PARTITION, VIEW

3 rows selected.

OWNER	OBJECT_TYPES
HR	INDEX, TABLE, VIEW
PUBLIC	SYNONYM
SH	INDEX, TABLE, TABLE PARTITION, VIEW

Statement 22

This groups the rows and uses DISTINCT to remove duplicates. As with the previous examples using LISTAGG as an aggregate and analytic, this can be confusing.

De-duplicating values in the list

select distinct    
  owner,    
  listagg ( object_type || ' (' || count(*) || ')', ', ' )    
    within group ( order by object_type )    
    over ( partition by owner ) object_types   
from   all_objects   
where  owner in ( 'HR', 'CO', 'SH' ) 
group  by owner, object_type   
order  by owner


OWNER OBJECT_TYPES
CO INDEX (11), TABLE (5), VIEW (4)
HR INDEX (19), TABLE (7), VIEW (1)
SH INDEX (29), TABLE (16), TABLE PARTITION (56), VIEW (1)

3 rows selected.

OWNER	OBJECT_TYPES
CO	INDEX (11), TABLE (5), VIEW (4)
HR	INDEX (19), TABLE (7), VIEW (1)
SH	INDEX (29), TABLE (16), TABLE PARTITION (56), VIEW (1)

Statement 23

This is the same as the previous SELECT, but split into separate queries to aid readability.

with schema_object_counts as (   
  select owner, object_type, count(*) c   
  from   all_objects    
  where  owner in ( 'HR', 'CO', 'SH' ) 
  group  by owner, object_type   
)   
  select    
    owner,    
    listagg ( object_type || ' (' || c || ') ', ', ' )   
      within group ( order by object_type ) objects   
  from   schema_object_counts   
  group  by owner   
  order  by owner


OWNER OBJECTS
CO INDEX (11) , TABLE (5) , VIEW (4) 
HR INDEX (19) , TABLE (7) , VIEW (1) 
SH INDEX (29) , TABLE (16) , TABLE PARTITION (56) , VIEW (1) 

3 rows selected.

OWNER	OBJECTS
CO	INDEX (11) , TABLE (5) , VIEW (4)
HR	INDEX (19) , TABLE (7) , VIEW (1)
SH	INDEX (29) , TABLE (16) , TABLE PARTITION (56) , VIEW (1)

Statement 24

Each LISTAGG expression can use DISTINCT

De-duplicating many lists

select    
  owner,    
  listagg ( distinct object_type, ', ' )    
    within group ( order by object_type ) object_types,    
  substr (    
    listagg ( distinct object_name, ', ' on overflow truncate )    
      within group ( order by object_name ) ,   
    1, 100    
  ) object_names   
from   all_objects   
where  owner in ( 'HR', 'CO', 'SH' ) 
group  by owner


OWNER OBJECT_TYPES OBJECT_NAMES
CO INDEX, TABLE, VIEW CUSTOMERS, CUSTOMERS_EMAIL_U, CUSTOMERS_NAME_I, CUSTOMERS_PK, CUSTOMER_ORDER_PRODUCTS, ORDERS, ORDER
HR INDEX, TABLE, VIEW COUNTRIES, COUNTRY_C_ID_PK, DEPARTMENTS, DEPT_ID_PK, DEPT_LOCATION_IX, EMPLOYEES, EMP_DEPARTMENT_IX,
SH INDEX, TABLE, TABLE PARTITION, VIEW CAL_MONTH_SALES_MV, CHANNELS, CHANNELS_PK, COSTS, COSTS_PROD_BIX, COSTS_TIME_BIX, COUNTRIES, COUNTRI

3 rows selected.

OWNER	OBJECT_TYPES	OBJECT_NAMES
CO	INDEX, TABLE, VIEW	CUSTOMERS, CUSTOMERS_EMAIL_U, CUSTOMERS_NAME_I, CUSTOMERS_PK, CUSTOMER_ORDER_PRODUCTS, ORDERS, ORDER
HR	INDEX, TABLE, VIEW	COUNTRIES, COUNTRY_C_ID_PK, DEPARTMENTS, DEPT_ID_PK, DEPT_LOCATION_IX, EMPLOYEES, EMP_DEPARTMENT_IX,
SH	INDEX, TABLE, TABLE PARTITION, VIEW	CAL_MONTH_SALES_MV, CHANNELS, CHANNELS_PK, COSTS, COSTS_PROD_BIX, COSTS_TIME_BIX, COUNTRIES, COUNTRI

Statement 25

Before 19c, if you wanted to to de-duplicate values in many LISTAGG expressions, you had to use a subquery like this.

De-duplicating many lists

with schema_object_counts as (   
  select owner, object_type, object_name,   
         row_number () over (    
           partition by owner, object_type   
           order by object_id   
         ) type_rn,   
         row_number () over (    
           partition by owner, object_name   
           order by object_id    
         ) name_rn   
  from   all_objects    
  where  owner in ( 'HR', 'CO', 'SH' ) 
)   
  select    
    owner,    
    listagg (    
      case when type_rn = 1 then object_type end, ', '    
    ) within group ( order by object_type ) object_types,    
    substr (    
      listagg (    
        case when name_rn = 1 then object_name end, ', '    
        on overflow truncate    
      ) within group ( order by object_name ),   
      1, 100 )    
    object_names   
  from   schema_object_counts   
  group  by owner   
  order  by owner


OWNER OBJECT_TYPES OBJECT_NAMES
CO INDEX, TABLE, VIEW CUSTOMERS, CUSTOMERS_EMAIL_U, CUSTOMERS_NAME_I, CUSTOMERS_PK, CUSTOMER_ORDER_PRODUCTS, ORDERS, ORDER
HR INDEX, TABLE, VIEW COUNTRIES, COUNTRY_C_ID_PK, DEPARTMENTS, DEPT_ID_PK, DEPT_LOCATION_IX, EMPLOYEES, EMP_DEPARTMENT_IX,
SH INDEX, TABLE, TABLE PARTITION, VIEW CAL_MONTH_SALES_MV, CHANNELS, CHANNELS_PK, COSTS, COSTS_PROD_BIX, COSTS_TIME_BIX, COUNTRIES, COUNTRI

3 rows selected.

How to combine rows into CSV strings using LISTAGG

Additional Information