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.
ReplyDeleteselect * from
ReplyDelete(
select
sal,
deptno,
job
from
scott.emp
)
pivot
(
sum(sal) for deptno in (10,20,30,40)
)
I dont want to hard code the in parameter..just it sholud be dynamic..wat should i need to do..
This comment has been removed by the author.
ReplyDeleteYou have written an excellent blog. I learned something new from your Content. Keep sharing this kind of informative blog.
ReplyDeleteOracle DBA Training in Chennai
Oracle DBA Online Training
Best Oracle DBA Training in Chennai