This article contains some of the tips for PIVOTING the recordset (output of the query) . PIVOTING is mainly used for reporting purpose.
Displaying the total number of employees department wise.
=============================================
- select deptno,count(deptno) from emp group by deptno;
Pivoting the same output.
=====================
- select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
-
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
-
sum(case when deptno=30 then 1 else 0 end) as deptno_30,
-
sum(case when deptno=40 then 1 else 0 end) as deptno_40
-
from emp;
Selecting employees name group by there job.
======================================
-
select max(case when job='CLERK'
-
then ename else null end) as clerks,
-
max(case when job='ANALYST'
-
then ename else null end) as analysts,
-
max(case when job='MANAGER'
-
then ename else null end) as mgrs,
-
max(case when job='PRESIDENT'
-
then ename else null end) as presi,
-
max(case when job='SALESMAN'
-
then ename else null end) as sales
-
from (
-
select job,
-
ename,
-
row_number()over(partition by job order by ename) rn
-
from emp
-
) x
-
group by rn
Reverse Pivoting
=============
- select dept.deptno,
-
case dept.deptno
-
when 10 then emp_cnts.deptno_10
-
when 20 then emp_cnts.deptno_20
-
when 30 then emp_cnts.deptno_30
-
end as counts_by_dept
-
from (
-
Select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
-
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
-
sum(case when deptno=30 then 1 else 0 end) as deptno_30
-
from emp
-
) emp_cnts,
-
(select deptno from dept where deptno <= 30) dept
Display in single column
=====================
- select case rn
-
when 1 then ename
-
when 2 then job
-
when 3 then cast(sal as char(4))
-
end emps
-
from (
-
select e.ename,e.job,e.sal,
-
row_number()over(partition by e.empno
-
order by e.empno) rn
-
from emp e,
-
(select *
-
from emp where job='CLERK') four_rows
-
where e.deptno=10
-
) x
Suppressing Repeating Values from a Result Set
========================================
- select to_number(
-
decode(lag(deptno)over(order by deptno),
-
deptno,null,deptno)
-
) deptno, ename
-
from emp
Findout the Difference of sum of sal among groups department wise
================================================== ==
- select d20_sal - d10_sal as d20_10_diff,
-
d20_sal - d30_sal as d20_30_diff
-
from (
-
select sum(case when deptno=10 then sal end) as d10_sal,
-
sum(case when deptno=20 then sal end) as d20_sal,
-
sum(case when deptno=30 then sal end) as d30_sal
-
from emp
-
) totals_by_dept
Also Check
Povoting - 2 
