PIVOT

PIVOT

PIVOT clause allows you to write a cross-tabulation query starting in Oracle 11g. This means that you can aggregate your results and rotate rows into columns.

SELECT * FROM

(

SELECT column1, column2

FROM tables

WHERE conditions

)

PIVOT

(

aggregate_function(column2)

FOR column2

IN ( expr1, expr2, … expr_n) | subquery

)

WHERE on_complete resultsetu coming out after pivot

ORDER BY expression [ ASC | DESC ];

Our first example will be a simple demonstration of the PIVOT syntax. Using the EMP table, we will sum the salaries by department and job, but transpose the sum for each department onto its own column. Before we pivot the salaries, we will examine the base data, as follows.

SQL> SELECT job
2  ,      deptno
3  ,      SUM(sal) AS sum_sal
FROM   emp
GROUP  BY
6         job
7  ,      deptno
ORDER  BY
9         job
10  ,      deptno;

JOB           DEPTNO    SUM_SAL
——— ———- ———-
ANALYST           20       6600
CLERK             10       1430
CLERK             20       2090
CLERK             30       1045
MANAGER           10       2695
MANAGER           20     3272.5
MANAGER           30       3135
PRESIDENT         10       5500
SALESMAN          30       6160
We will now pivot this data using the new 11g syntax. For each job, we will display the salary totals in a separate column for each department, as follows.

SQL> WITH pivot_data AS (
2          SELECT deptno, job, sal
3          FROM   emp
4          )
SELECT *
FROM   pivot_data
  7  PIVOT (
8             SUM(sal)       

9         FOR deptno         

10         IN  (10,20,30,40)  

11        );

JOB               10         20         30         40
——— ———- ———- ———- ———-
CLERK           1430       2090       1045
SALESMAN                              6160
PRESIDENT       5500
MANAGER         2695     3272.5       3135
ANALYST                    6600
Other way to write

SQL>
SELECT *
FROM   (
2          SELECT deptno, job, sal
3          FROM   emp
4          )
  7  PIVOT (
8             SUM(sal)       

9         FOR deptno         

10         IN  (10,20,30,40)  

11        );

We can see that the department salary totals for each job have been transposed into columns. There are a few points to note about this example, the syntax and the results:

  • Line 8: our pivot_clause sums the SAL column. We can specify multiple columns if required and optionally alias them (we will see examples of aliasing later in this article);
  • Lines 1-4: pivot operations perform an implicit GROUP BY using any columns not in the pivot_clause (in our example, JOB and DEPTNO). For this reason, most pivot queries will be performed on a subset of columns, using stored views, inline views or subqueries, as in our example;
  • Line 9: our pivot_for_clause states that we wish to pivot the DEPTNO aggregations only;
  • Line 10: our pivot_in_clause specifies the range of values for DEPTNO. In this example we have hard-coded a list of four values which is why we generated four pivoted columns (one for each value of DEPTNO). In the absence of aliases, Oracle uses the values in the pivot_in_clause to generate the pivot column names (in our output we can see columns named “10”, “20”, “30” and “40”).
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s