SQL Pivot Statement

This post reviews the PIVOT statement introduced with the Oracle database, version 11g.  Tables in relational databases are displayed in a tabular format with a column-value pair structure.  Consider the table named EMPLOYEES. Using the desc command, the table structure is shown below:

SQL> desc employees;

Name                     Null?               Type
------------       --------------       -------------
EMPLOYEE_ID         NOT NULL        NUMBER(4)
FIRST_NAME                               VARCHAR2(25)
LAST_NAME           NOT NULL        VARCHAR2(25)
BRANCH_ID            NOT NULL        VARCHAR2(3)
CALLS_MADE                               NUMBER(5)

If the following query is run:
SELECT employee_id,branch_id,calls_made
FROM employees
ORDER BY employee_id;
The output would be as follows:

EMPLOYEE_ID BRANCH_ID   CALLS_MADE
----------       ----------          ---------------
1                  BR1                 7
2                  BR2                10
3                  BR3                 5 
... etc....

Here, the data is represented as rows of values. For each employee, the output shows the branch_id and number of calls made by each.  As an employee makes more calls, the column CALLS_MADE is updated.

Now suppose you want a report of calls made by Branch, and a count of  employees who made 1 call , 2 calls, 3 calls, etc.  You would write the following SQL query:

SELECT branch_id, calls_made, count(1) cnt
FROM employees
GROUP BY branch_id, calls_made;

Here is how the output would appear:

BRANCH_ID     CALLS_MADE          COUNT(1) CNT
------------       --------------            ----------------
BR2                       1                                51
BR2                       2                                19
BR2                       3                                15
BR2                       4                                24
BR2                       5                                19
... etc. ...

This is the data you requested but it could be difficult to read. Another way to represent the same data would be through a cross-tab report, where the calls_made is shown vertically and branch_id's shown horizontally, in a spreadsheet format:

Calls_made            BR1          BR2              BR3
-----------     -------       -------           ---------
1                          60             51                 40
2                          13             19                 17
3                          11             15                 10
4                          10             24                 15
5                           8              19                 12
... etc. ...

Prior to Oracle Database 11g, you could have used the Decode function for each value and write each distinct value as a separate column. However, this approach is not intuitive per se. Now, you can use the Oracle Pivot statement for obtaining results in a cross-tab format.  Depicted below is how you would write the query:

SELECT * FROM (
SELECT calls_made, branch_id
FROM employees
)
PIVOT
(
count(branch_id)
FOR branch_id in ('BR1','BR2','BR3')
)
ORDER BY calls_made;
Here is the output:
CALLS_MADE         'BR1'         'BR2'                 'BR3'
---------------   ----------     ----------       ---------------
1                          60             51                 40
2                          13             19                 17
3                          11             15                 10
4                          10             24                 15
5                           8              19                 12
... etc. ...

This shows the utility of the pivot operator. The branch id's are presented on the header row, instead of a column. In a crosstab report, the CALLS_MADE column is transposed to the header row.  In other words, the column becomes the row, such that the column is rotated 90 degrees anti-clockwise to become the header row. This rotation requires a pivot point and in this situation, the pivot point is the count(branch_id) expression.

There you have it, a nice addition to your SQL toolkit.