Oracle: PIVOT data (rows to columns)

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

4 comments:

  1. 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
  2. select * from
    (
    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..

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. You have written an excellent blog. I learned something new from your Content. Keep sharing this kind of informative blog.
    Oracle DBA Training in Chennai
    Oracle DBA Online Training
    Best Oracle DBA Training in Chennai

    ReplyDelete