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.
ReplyDeleteFor drivers in the area, a professional Clutch Mechanic Brighton offers expert solutions for all makes and models of cars and light commercial vehicles. From clutch inspections to complete clutch kit replacements, experienced mechanics ensure every repair is completed to the highest standard. Their goal is to restore smooth gear changes and improve your vehicle's overall performance.
ReplyDelete