Hi friends today we will discuss about LISTAGG in ORACLE..

SYNTAX:

LISTAGG(<Column Name>,”) WITHIN GROUP(ORDER BY <Column Name1>,<Column Name2>)

Example:

CREATE TABLE EMPLOYE
(
ENO VARCHAR2(10),
DNO VARCHAR2(10),
ENAME VARCHAR2(20),
SALARY VARCHAR2(10)
);

SELECT * FROM EMPLOYE;

ENO	DNO	ENAME	SALARY
101	10	RAJESH	18000
102	10	SURESH	28000
103	11	NARAYANA	18000
104	11	KIRAN	18000

From this we need the department wise employee names as in single row with
separated by ‘; ’ .

SELECT LISTAGG(ENAME,’; ‘) WITHIN GROUP(ORDER BY DNO) as EMPLOYEE NAMES,  DNO  AS Dept.No.
FROM   EMPLOYE   GROUP   BY   DNO;

Result:

EMPLOYEE NAMES

Dept.No.

RAJESH; SURESH

10

KIRAN; NARAYANA

11