This is part of Oracle SQL tutorial and has good examples, explanation on Oracle LISTAGG Function.
Oracle LISTAGG function
Oracle LISTAGG function is an analytical function which allows us to concatenate the strings for measure_column for each GROUP based on the order_by_clause. This is present in Oracle from 11gR2
The syntax for the LISTAGG function in Oracle is

LISTAGG (measure_column [, 'delimiter'])
WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]
Explanation of terms
measure_column | The column or expression whose values you wish to concatenate together in the result set. Null values in the measure_column are ignored. |
Delimiter | Optional. It is the delimiter to use when separating the measure_column values when outputting the results. |
order_by_clause | It determine the order in which the concatenated values are returned |
Lets see some cases and example on LISTAGG function
1) As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
SELECT LISTAGG(first_name, '; ')
WITHIN GROUP (ORDER BY hire_date, last_name) "Employee_list",
MIN(hire_date) "Earliest"
FROM emp
WHERE dept_no = 30;Employee_list Earliest
------------------------------------------------------------ ---------
TOM; BOB; BILL 17-JUN-18
2) As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
COLUMN employees FORMAT A50
SELECT deptno, LISTAGG(ename, ';') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 JOSHUA,KING,MILLER
20 AJAY,FANES,SCOTT,SMITH
30 TOM; BOB; BILLMore Example
select table_name,
listagg(index_name, ',') within group (order by index_name) all_inds
from user_indexes
group by table_name;
3)As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.
SQL> SELECT deptno
, ename
, hiredate
, LISTAGG(ename, ',')
WITHIN GROUP (ORDER BY hiredate)
OVER (PARTITION BY deptno) AS employees
FROM emp order by deptno;DEPTNO ENAME HIREDATE EMPLOYEES
---------- ---------- ----------- -------------------------------------
10 JOSHUA 09/06/2018 JOSHUA,KING,MILLER
10 KING 17/11/2018 JOSHUA,KING,MILLER
10 MILLER 23/01/2018 JOSHUA,KING,MILLER
20 AJAY 17/12/2018 AJAY,FANES,SCOTT,SMITH
20 FANES 02/04/2018 AJAY,FANES,SCOTT,SMITH
20 SCOTT 19/04/2018 AJAY,FANES,SCOTT,SMITH
20 SMITH 23/05/2018 AJAY,FANES,SCOTT,SMITH
30 TOM 20/02/2018 TOM; BOB; BILL
30 BOB 22/02/2018 TOM; BOB; BILL
30 BILL 01/05/2018 TOM; BOB; BILL
Addition in LISTAGG function from Oracle database 12cR2
The maximum no of characters returns is 4000 bytes and if it exceeds ,it gives the error
ORA-01489: result of string concatenation is too long
With Oracle 12cR2 , Oracle has provided an clause on overflow truncate to handle overflow errors gracefully
listagg (
measure, ','
[ on overflow (truncate|error) ]
[ text ] [ (with|without) count ]
) within group (order by cols)
Now you can explicitly say whether you want error or truncation semantics. The pre 12cR2 codes works fine as that is the default behavior
Now suppose ,you dont want to return error when it crosses 4k bytes, then on overflow truncate is the solution.
select table_name,
listagg(index_name, ',' on overflow truncate) within group (order by index_name) inds
from user_indexes
group by table_name;
In-case truncation occurs ,Oracle will truncate back to the next full value at which point you can control how you tell the user that the list has been truncated. By default we append three dots ‘…’ to the string as indicator that truncation has occurred. You can change the ‘….’ if you want you can override that
If you want to replace “…” with “more”, “extra” or a “click for more” hyperlink, just provide your new string!
select table_name,
listagg(index_name, ',' on overflow truncate
'|||'
) within group (order by index_name) inds
from user_indexes
group by table_name;
By default , truncate shows the count of missing values If dont want to show the count,then use without count
select table_name,
listagg(index_name, ',' on overflow truncate '....' without count) within group (order by index_name) inds
from user_indexes
group by table_name;
Pre 11GR2 solution (10g, 9i , 11gR1)
If you are not running 11g Release 2 or above, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.
COLUMN employees FORMAT A50
SELECT deptno, wm_concat(ename) AS employees
FROM emp
GROUP BY deptno;
EPTNO EMPLOYEES
---------- --------------------------------------------------
10 JOSHUA,KING,MILLER
20 AJAY,FANES,SCOTT,SMITH
30 TOM; BOB; BILL
This can also be achieved through user defined function. I would recommend checking the below asktom link. This is a must read
I hope you like the content of this post on Oracle LISTAGG Function
Related ArticlesAuto Increment in Oracle
Oracle Joins
Sql Set Operators
How to use google translate URL in Oracle plsql
Single row functions in sql
date function in oracle
Leave a Reply