Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
Pivot and Unpivot Operators
Pivoting tables are now possible in 11g through PIVOT clause. The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. Prior to 11g new functions it was possible to accomplish by using DECODE combined with some aggregate function like SUM.
Also, Adding the XML keyword to the PIVOT operator allows us to convert the generated pivot results to XML format. It also makes the PIVOT a little more flexible, making possible to replace the hard coded IN clause with a subquery, or the ANY commands.
In same way, the UNPIVOT operator converts column-based data into separate rows. Some important considerations about feature:
- Column names are required. These can be set to any name not currently in the driving table.
- The columns to be unpivoted must be named in the IN clause.
- By default the EXCLUDE NULLS clause is used. To override the default behavior use the INCLUDE NULLS clause.
- The following query shows the inclusion of the INCLUDE NULLS clause.
For more information and examples: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1
Cheers!
