Oracle SQL listagg Function

Oracle SQL listagg function is analytic function and can be used for generating aggregate lists.

Steps to demonstrate Oracle SQL listagg function

1. Create employee table
   1. create table employee ( employee_number number, employee_name varchar2(50), department_number number);
   2. create table department ( department_number number, departmen_name varchar2(50));
2. Insert data into department table
   1. insert into department values (10,’IT’);
   2. insert into department values (11,’SALES’);
   3. insert into department values (12,’FINANCE’);
   4. insert into department values (13,’HR’);
   5. insert into department values (14,’ADMINISTRATION’);
3. Insert date into employee table
   1. INSERT into employee values (1,’John Doe’ , 10);
   2. INSERT into employee values (1,’foo’ , 10);
   3. INSERT into employee values (1,’Adam’ , 11);
   4. INSERT into employee values (1,’SAM’ , 11);
   5. INSERT into employee values (1,’Mary’ , 11);
   6. INSERT into employee values (1,’Linda’ , 12);

4. Oracle SQL listagg function to generate employee list for deparments

SELECT d.department_name
      ,LISAGG(e.employee_name,';') WITHIN GROUP ( ORDER BY d.department_name ) list_of_employee
FROM employee   e
    ,department d
WHERE e.dpartment_number = d.department_number
GROUP BY d.department_name;