Tech Trainings

LISTAGG – Display rows in Columns in CSV Format – Oracle Database

February 23, 2025 by admin

•As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

•As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.

•As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.

select  
listagg (e.EMPNO,', ' on overflow truncate with count)
within group (order by e.EMPNO) empno 
from scott.emp e;

select d.dname,
listagg (e.EMPNO,', ' on overflow truncate with count)
within group (order by e.EMPNO) emplist
from scott.dept d, scott.emp e
where d.deptno = e.deptno
group by d.dname  ; 

select d.dname,
listagg ( e.job,', ' on overflow truncate with count)
within group (order by e.job) jobs
from scott.dept d, scott.emp e
where d.deptno = e.deptno
group by d.dname ;

--With Distinct
select d.dname,
listagg (DISTINCT e.job,', ' on overflow truncate with count)
within group (order by e.job) jobs
from scott.dept d, scott.emp e
where d.deptno = e.deptno
group by d.dname ;

--LISTAGG with ROWNUM
SELECT
    LISTAGG(e1.empno, ', ' ON OVERFLOW TRUNCATE WITH COUNT) WITHIN GROUP(
    ORDER BY
        e1.empno
    ) empno
FROM
    (
        SELECT
            e.empno
        FROM
            scott.emp e
        WHERE
            rownum <= 5
    ) e1
    ;

Comments |0|