11g has a very good new feature for SQL*PLUS called PIVOT and UNPIVOT - it is very useful for
creating cross tabular reports and aggregates.
In earlier Oracle versions this would have needed a lot of coding using the DECODE function ....
Objective - find the count of employees in each department broken down by the job performed.
SQL>
select * from
(select deptno,job from emp
)
PIVOT
(
count(*)
for job in ('SALESMAN','CLERK','MANAGER','ANALYST')
)
;
SQL>
DEPTNO 'SALESMAN' 'CLERK' 'MANAGER' 'ANALYST'
---------- ---------- ---------- ---------- ----------
30 4 1 1 0
20 0 2 1 2
10 0 1 1 0
This operation can be reversed using the UNPIVOT command
SQL>
select * from pivot_emp
UNPIVOT
(employee_count for job in ("'SALESMAN'","'CLERK'","'MANAGER'","'ANALYST'")
);
DEPTNO JOB EMPLOYEE_COUNT
---------- ---------- --------------
30 'SALESMAN' 4
30 'CLERK' 1
30 'MANAGER' 1
30 'ANALYST' 0
20 'SALESMAN' 0
20 'CLERK' 2
20 'MANAGER' 1
20 'ANALYST' 2
10 'SALESMAN' 0
10 'CLERK' 1
10 'MANAGER' 1
10 'ANALYST' 0
12 rows selected.
Objective - find count which year were most employees hired in
SQL> select empno,hiredate from emp;
EMPNO HIREDATE
---------- ---------
7369 17-DEC-80
7499 20-FEB-81
7521 22-FEB-81
7566 02-APR-81
7654 28-SEP-81
7698 01-MAY-81
7782 09-JUN-81
7788 19-APR-87
7839 17-NOV-81
7844 08-SEP-81
7876 23-MAY-87
7900 03-DEC-81
7902 03-DEC-81
7934 23-JAN-82
14 rows selected.
SQL>
select count(*), extract (year from hiredate) year_hired from emp
group by extract (year from hiredate);
COUNT(*) YEAR_HIRED
---------- ----------
1 1982
2 1987
1 1980
10 1981
0 Comments