PIVOT & UNPIVOT 11g

PIVOT & UNPIVOT 11g

PIVOT Example

Consider following example in which we are fetching total salary for each JOB within the department (DEPTNO)

 

To convert above output into matrix form (.xls style) we used to use following query in all earlier versions.

 

From oracle 11g onwards we can use PIVOT option

SELECT * FROM (SELECT deptno,job,sal from emp)

PIVOT (sum(sal) FOR job IN('ANALYST' "Analyst",
'CLERK' "Clerk",
'MANAGER' "Manager",
'PRESIDENT' "President",
'SALESMAN' "Salesman"
))ORDER BY deptno

UNPIVOT Example

create table marks_list(

regno integer primary key,
name varchar2(12),
physics integer,
chemistry integer,
maths integer,
biology integer);

insert into marks_list values(1001,'RAJESH',89,78,92,77);

insert into marks_list values(1002,'SACHIN',76,68,72,67);
insert into marks_list values(1003,'SWATHI',86,88,82,87);

select * from marks_list;

select * from marks_list

UNPIVOT(marks for subjects IN (
physics as 'PHYSICS', --alias naming optional
chemistry as 'CHEMISTRY',
maths as 'MATHEMATICS',
biology as 'BIOLOGY'))
order by regno;