ON ORACLE 11g
select * from
(
select
sal,
deptno,
job
from
scott.emp
)
pivot
(
sum(sal) for deptno in (10,20,30,40)
)
--------------------------------------------------------
BEFORE ORACLE 11g
select *
from (select job,
sum(decode(deptno,10,sal)) dept10,
sum(decode(deptno,20,sal)) dept20,
sum(decode(deptno,30,sal)) dept30,
sum(decode(deptno,40,sal)) dept40
from scott.emp
group by job)
order by 1;
OUTPUT
select * from
(
select
sal,
deptno,
job
from
scott.emp
)
pivot
(
sum(sal) for deptno in (10,20,30,40)
)
--------------------------------------------------------
BEFORE ORACLE 11g
select *
from (select job,
sum(decode(deptno,10,sal)) dept10,
sum(decode(deptno,20,sal)) dept20,
sum(decode(deptno,30,sal)) dept30,
sum(decode(deptno,40,sal)) dept40
from scott.emp
group by job)
order by 1;
OUTPUT
This post gives you syntax for using Oracle pivot. The syntax is very easy. You can see the simplicity in code with the help of pivot as before 11g the code is bit complex. The new syntax is easy to learn also. Thanks for this short method.
ReplyDelete