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);
Data in Department Table
Data in Employee Table
Join between Employee and Department Table
  1. 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;
Oracle SQL listagg function

Leave a Comment

Your email address will not be published.