LISTAGG and PIVOT in ORACLE 11g

The two new features of ORACLE 11g - The LISTAGG function and the PIVOT SQL clause - are very handy.

The benefit with the same is actually visible when you compare these features with the "old" technique to get the same output.
We can see the differences with the sample EMP and DEPT created and populated as below.

drop table test_emp;
drop table test_dept;

create table test_emp(emp_id number(5),emp_name varchar2(150),
dept_id number(4));
create table test_dept(dept_id number(4),dept_name varchar2(150));
Insert into TEST_DEPT
(DEPT_ID, DEPT_NAME)
Values (30, 'Sales');
Insert
into TEST_DEPT
(DEPT_ID, DEPT_NAME)
Values (20, 'IT');
Insert into TEST_DEPT
(DEPT_ID, DEPT_NAME)
Values
(10, 'HR');
COMMIT;
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (6, 'Sabu Samuel', 30);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (6, 'Chinthaka G', 30);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (5, 'Margarat K Jack', 20);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (4, 'Kennet Lane', 20);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (3, 'Rama Murthy', 10);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (2, 'Wilson Dslva', 10);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (1, 'Lawrence Clinch', 10);
COMMIT;
Prior to 11g to get the Department name as a column and count as rows we need to use the SUM(DECODE()) technique.

select sum(decode(dept_name,'HR',1,0)) hr,
sum(decode(dept_name,'IT',1,0)) it,
sum(decode(dept_name,'Sales',1,0)) sales
from test_emp e,test_dept d
where e.DEPT_ID = d.DEPT_ID
group by dept_name; 

To Get a list of employees across departments in a Pivoted manner we need to use SUM(DECODE()) technique along with SYS_CONNECT_BY_PATH prior to 11g.

select max(decode(dept_name,'HR',emp_list)) hr,
max(decode(dept_name,'IT',emp_list)) it,
max(decode(dept_name,'Sales',emp_list)) sales
from (
select dept_name,ltrim(max(sys_connect_by_path(emp_name,',')),',') emp_list
from(
select dept_name,emp_name,row_number() over(partition by dept_name order by emp_name) rn
from test_emp e,test_dept d
where e.DEPT_ID = d.DEPT_ID
)
start with rn = 1
connect by prior rn = rn - 1 and prior dept_name = dept_name
group by dept_name
);

HR                                       IT                        SALES
---------------------------------------- ------------------------- -------------------------
Lawrence Clinch,Rama Murthy,Wilson Dslva Kennet Lane,Margarat K Ja Chinthaka G,Sabu Samuel

In 11g the same thing can be achieved by a simple and fast query as shown below.

select *
from(
select dept_name,listagg(emp_name) within group (order by emp_name) emp_list
from test_emp e,test_dept d
where e.DEPT_ID = d.DEPT_ID
group by dept_name
) pivot ( max(emp_list) for dept_name in ('HR','IT','Sales') ); 

Tags: LISTAGG ORACLE 11g , PIVOT ORACLE 11g