How to use Oracle LISTAGG Function

This is part of the Oracle SQL tutorial and has good examples and explanations of the Oracle LISTAGG Function.

Oracle LISTAGG function

Oracle LISTAGG function is an analytical function that 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

Let’s see some cases and examples on the 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 expressions 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 number of characters returned is 4000 bytes and if it exceeds that, it gives the error

ORA-01489: the result of string concatenation is too long

With Oracle 12cR2, Oracle has provided a 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 work fine as that is the default behavior

Now suppose, you dont want to return an 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 an 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 an example of a user-defined aggregate function described below, but Oracle has 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 functions. I would recommend checking the below ask tom link. This is a must-read

Oracle LISTAGG function

Addition in LISTAGG function from Oracle database 12cR2

Related Posts

Leave a Comment Cancel Reply