One of the most frequent problems in SQL is the need to represent as columns data contained in different rows of a database tables. This problem is usually resolved using the UNION operators, in Oracle11g we have a native tool that can help: PIVOT clause.

An example can explain better than 1000 words: the EMP table (old good SCOTT/TIGER schema) contains a record for each employee. Among others there are the following columns: ENAME (employee’s name), DEPTNO (department’s code) e SAL (employee’s salary). We need to extract a report with four columns: in the first one we want the employee’s name, in the second one the sum of salaries of employees that work in the department #10, in the third one the sum of salaries of employees that work in the department #20, in the forth one the sum of salaries of employees that work in the department #30. First of all let’s see how to resolve it with an UNION:

select ename, sal d10, null d20, null d30 from emp where deptno=10 union select ename, null d10, sal d20, null d30 from emp where deptno=20 union select ename, null d10, null d20, sal d30 from emp where deptno=30 ; ENAME D10 D20 D30 ---------- ---------- ---------- ---------- ADAMS 1100 ALLEN 1600 BLAKE 2850 CLARK 2450 FORD 3000 JAMES 950 JONES 2975 KING 5000 MARTIN 1250 MILLER 1300 SCOTT 3000 SMITH 800 TURNER 1500 WARD 1250

In Oracle11g we can use the PIVOT clause. This operator let us to define new columns specifying: the values of the filter column (DEPTNO in our example), the column to use for the calculation (SALARY) and the group function to apply (SUM):

select ename, d10,d20,d30 from emp pivot (sum(sal) for deptno in (10 as D10, 20 as d20, 30 as d30)); ENAME D10 D20 D30 ---------- ---------- ---------- ---------- MARTIN 1250 BLAKE 2850 MILLER 1300 WARD 1250 JONES 2975 TURNER 1500 JAMES 950 KING 5000 ADAMS 1100 FORD 3000 CLARK 2450 SCOTT 3000 SMITH 800 ALLEN 1600

Once you get this first result we can write more complex statements to get more: the sum of salaries by department (with results in different columns):

select sum(d10),sum(d20),sum(d30) from emp pivot (sum(sal) for deptno in (10 as D10, 20 as d20, 30 as d30)); SUM(D10) SUM(D20) SUM(D30) ---------- ---------- ---------- 8750 10875 9400

The sum of salaries by department (on columns) and job (on rows):

select job, sum(d10),sum(d20),sum(d30) from emp pivot (sum(sal) for deptno in (10 as D10, 20 as d20, 30 as d30)) group by job; JOB SUM(D10) SUM(D20) SUM(D30) --------- ---------- ---------- ---------- SALESMAN 5600 CLERK 1300 1900 950 PRESIDENT 5000 MANAGER 2450 2975 2850 ANALYST 6000

Here’s the link to documentation for further details…

Massimo

## Leave a Reply