Transforming rows to columns in Oracle SQL – The PIVOT clause

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

Advertisements

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: