By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,082 Members | 573 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Pivoting - 1

debasisdas
Expert 5K+
P: 8,127
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.
=============================================
Expand|Select|Wrap|Line Numbers
  1. select deptno,count(deptno) from emp group by deptno;
Pivoting the same output.
=====================
Expand|Select|Wrap|Line Numbers
  1. select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
  2.         sum(case when deptno=20 then 1 else 0 end) as deptno_20,
  3.         sum(case when deptno=30 then 1 else 0 end) as deptno_30,
  4.         sum(case when deptno=40 then 1 else 0 end) as deptno_40
  5.    from emp;
Selecting employees name group by there job.
======================================
Expand|Select|Wrap|Line Numbers
  1. select max(case when job='CLERK'
  2.                  then ename else null end) as clerks,
  3.         max(case when job='ANALYST'
  4.                  then ename else null end) as analysts,
  5.         max(case when job='MANAGER'
  6.                  then ename else null end) as mgrs,
  7.         max(case when job='PRESIDENT'
  8.                  then ename else null end) as presi,
  9.         max(case when job='SALESMAN'
  10.                  then ename else null end) as sales
  11.   from (
  12. select job,
  13.        ename,
  14.        row_number()over(partition by job order by ename) rn
  15.   from emp
  16.        ) x
  17.  group by rn
Reverse Pivoting
=============
Expand|Select|Wrap|Line Numbers
  1. select dept.deptno,
  2.            case dept.deptno
  3.                 when 10 then emp_cnts.deptno_10
  4.                 when 20 then emp_cnts.deptno_20
  5.                 when 30 then emp_cnts.deptno_30
  6.            end as counts_by_dept
  7.       from (
  8.     Select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
  9.            sum(case when deptno=20 then 1 else 0 end) as deptno_20,
  10.            sum(case when deptno=30 then 1 else 0 end) as deptno_30
  11.       from emp
  12.            ) emp_cnts,
  13.            (select deptno from dept where deptno <= 30) dept

Display in single column
=====================
Expand|Select|Wrap|Line Numbers
  1. select case rn
  2.             when 1 then ename
  3.             when 2 then job
  4.             when 3 then cast(sal as char(4))
  5.        end emps
  6.   from (
  7. select e.ename,e.job,e.sal,
  8.        row_number()over(partition by e.empno
  9.                             order by e.empno) rn
  10.   from emp e,
  11.        (select *
  12.           from emp where job='CLERK') four_rows
  13.  where e.deptno=10
  14.        ) x
Suppressing Repeating Values from a Result Set
========================================
Expand|Select|Wrap|Line Numbers
  1. select to_number(
  2.               decode(lag(deptno)over(order by deptno),
  3.                     deptno,null,deptno)
  4.            ) deptno, ename
  5.       from emp
Findout the Difference of sum of sal among groups department wise
================================================== ==
Expand|Select|Wrap|Line Numbers
  1. select d20_sal - d10_sal as d20_10_diff,
  2.        d20_sal - d30_sal as d20_30_diff
  3.   from (
  4. select sum(case when deptno=10 then sal end) as d10_sal,
  5.        sum(case when deptno=20 then sal end) as d20_sal,
  6.        sum(case when deptno=30 then sal end) as d30_sal
  7.   from emp
  8.        ) totals_by_dept
Also Check Povoting - 2
Sep 17 '07 #1
Share this Article
Share on Google+