Analytic Functions in Oracle - VI

In this post, we will focus on pivoting operation in Oracle. Pivot operation in Oracle was introduced in 11g version

AS in earlier posts, we will use Pivoting operation in Oracle lets one to aggregate rows followed by rotating the rows into columns.  To get an idea, let us understand run below query:

select department_id, count(*) from hr.employees where department_id <= 40 group by department_id;

The results returned are shown below: 

We can rotate the values in department_id column to be column headers using PIVOT keyword. Let us run below query for starters that will be used in the PIVOT query.

select department_id from hr.employees  where department_id <= 40;

The results returned are shown below: 

Now, let us use the PIVOT query as shown below:

select * from (select department_id from hr.employees where department_id <= 40) pivot (count(*) for department_id in (10,20,30,40));

The results returned are shown below: 

The results show that an aggregate, count, of the departments is calculated on the fly and then the values in the department column is rotated to be column headers.  The expression following PIVOT keyword can be understood in the following way:

(count(*) for department_id in (10,20,30,40))

COUNT(*) is the aggregated entity, and will appear as a row. department_id following the FOR keyword is the column header. Expression (10,20,30,40) following the IN clause filters the column appearing after FOR keyword, department_id in this case.

The filtering of the columns in the final result can be done in the values in the parenthesis after IN keyword as shown in below query:

 select * from (select department_id from hr.employees) pivot (count(*) for department_id in (10,20,30,40));

The results returned are shown below: 

The results are the same as before.

Let us now add one more column called salary instead of count aggregate. The original query for reference is shown below:

select department_id, sum(salary) from hr.employees where department_id <= 40 group by department_id order by department_id;

The results returned are shown below:

 
The query with PIVOT keyword is shown below:

select * from (select department_id, salary from hr.employees) pivot (sum(salary)  for department_id in (10,20,30,40));

The results returned are shown below: 

The results are as expected.

Next, we take a look at aliasing of the resulting column headers. As before, let us check the reference query first.

select department_id, sum(salary) "Sum of Salary" from hr.employees where department_id <= 40 group by department_id order by department_id;

The results returned are shown below:

We see the salary column is aliased as expected.

The first query we see with PIVOT keyword is shown below:

select * from (select department_id, salary from hr.employees) pivot (sum(salary) "Sum of Salary" for department_id in (10,20,30,40));

The results returned are shown below: 

We see column header is a concatenation of the aggregate alias and the elements in IN clause. The next query has aliases for the elements in the IN clause as shown below:

select * from (select department_id, salary from hr.employees) pivot (sum(salary) "Sum of Salary" for department_id in (10 "A" ,20 "B",30 "C",40 "D") );

The results returned are shown below: 

Now, we see that the column header is a concatenation of the aggregate alias and the aliases in IN clause. Before we invoke the next query, let us run the reference that shows two aggregates:

select department_id, count(*), sum(salary) "Sum of Salary" from hr.employees where department_id <= 40 group by department_id order by department_id;

The results returned are shown below:

Now, we will replicate the above result but with the pivot operation with these two aggregates and also having aliases.

select * from (select department_id,salary from hr.employees ) pivot (count(*) as cnt,sum(salary) as sal for department_id in (10,20,30,40));

The results returned are shown below:

 The results are in line with our expectation that the aggregate alternate each other starting with COUNT aggregate.

The way to filter the results based on input in IN clause is shown in the next few examples. To get an idea, let us understand run below query:

select department_id, sum(salary) "Sum of Salary" from hr.employees where department_id <= 40 group by department_id order by department_id;

The results returned are shown below:

Let us run below query that filters department_id as shown below:

select * from (select department_id, salary from hr.employees) pivot (sum(salary) "Sum of Salary" for department_id in (select department_id from hr.employees where department_id <= 40));

We get below error:

ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:   
*Action:
Error at Line: 42 Column: 121

But, adding XML keyword after PIVOT keyword solves the issue as shown below:

select * from (select department_id, salary from hr.employees) pivot  xml (sum(salary) "Sum of Salary" for department_id in (select department_id from hr.employees where department_id <= 40));

The results returned are shown below: 

We get a result with xml tags that shows the filtered result. Opening the result saved as a .xml file, we see the below result filtered by department_id:

Lastly, we see the UNPIVOT operation. It is the reverse of the PIVOT operation as shown in below query:

select value_type department_id,value cnt
from
(
    (
      select * from (select department_id from hr.employees)
pivot (count(*) for department_id in (10 "10",20 "20",30  "30",40  "40"))
    )
    unpivot
    (
        value
        for value_type in
            ("10","20","30","40")
    )
);
  
The results returned are shown below: 

The results are the same as returned by the first query in this blog.This concludes the post on pivoting in Oracle.