LISTAGG Analytic Function Oracle 11g feature

Learn how to concatenate the column wise values of a table and display the result in a single row using LISTAGG analytical function.

LISTAGG function allows the table column values to be displayed with in a single row.

Performs string concatination of the table column values.

Listagg is typically used to denormalize rows into a string of comma-separated values (CSV) or other comparable formats suitable for human reading.

Listagg removes null values before aggregation like most other aggregate functions. If no not null value remains, the result of listagg is null. If needed, coalesce can be used to replace null values before aggregation.

The return type of listagg is either varchar or clob with an implementation defined length limit. In practice, it is a varchar type.

Below is the syntax for Listagg

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

measure_expr can be any expression. Null values in the measure column are ignored.

The delimiter_expr designates the string that is to separate the measure values. This clause is optional and defaults to NULL.

The order_by_clause determines the order in which the concatenated values are returned.

Execute the below scripts to understand the LISTAGG function

create table EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
)


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 300.00, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 500.00, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('09-12-1982', 'dd-mm-yyyy'), 3000.00, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('12-01-1983', 'dd-mm-yyyy'), 1100.00, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10);

SELECT * FROM EMP;

Listagg Query Examples

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

Query Example 1 without delimiter

SELECT LISTAGG(ENAME) WITHIN GROUP(ORDER BY ENAME ) AS LIST_OF_EMP_NAMES FROM EMP;

Query Example 2 with delimiter

SELECT LISTAGG(ENAME,',') WITHIN GROUP(ORDER BY ENAME ) AS LIST_OF_EMP_NAMES FROM EMP;

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

SELECT LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME) AS LIST_OF_EMP_NAMES
  FROM EMP
 GROUP BY DEPTNO;

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

SELECT LIST_OF_EMP_NAMES
  FROM (
        
        SELECT DEPTNO,
                LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME) OVER(PARTITION BY DEPTNO) AS LIST_OF_EMP_NAMES,
                RANK() OVER(PARTITION BY DEPTNO ORDER BY ENAME) RNK
          FROM EMP)
 WHERE RNK = 1;