• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » How to use Oracle LISTAGG Function

How to use Oracle LISTAGG Function

September 16, 2022 by techgoeasy Leave a Comment

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

Oracle LISTAGG Function
LISTAGG (measure_column [, 'delimiter'])
WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]

Explanation of terms

measure_columnThe column or expression whose values you wish to concatenate together in the result set. Null values in the measure_column are ignored.
DelimiterOptional. It is the delimiter to use when separating the measure_column values when outputting the results.
order_by_clauseIt 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

Related Articles
Auto 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

Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us