How to use Oracle LISTAGG Function

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; BILL
More 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

Listagg alternative option

I hope you like the content of this post on Oracle LISTAGG Function

Oracle LISTAGG function

Addition in LISTAGG function from Oracle database 12cR2

Reader Interactions

Leave a Reply Cancel reply