SQL – Pivot and Unpivot

Pivot

                                              

This week’s topic is aimed at understanding how database developers can deliver pivot and unpivot data orientation to data output. The Pivot and UnPivot clauses are new to Oracle Database 11g, the database that I am using in this article. The development environment I am using in the making of the screen captures is Oracle Developer Studio IDE. Often times Excel users need to take database output through reports they receive and massage it themselves in excel by creating pivot tables in order to view the data in a way that allows for identification of trends. For exactly the same purposes, we can do this across larger sets of data in the database itself leveraging its ability to process more complex code designs than otherwise practical in Excel. Further, it is possible to perform an aggregation function upon the data. In my example, I have an employee table where you will see that we have monthly salary and department data stored. Employee names and departments listed are meaningless for demonstration purposes. Following is the employee table, which we are going to pull the salary of each individual, and the department number that they are assigned. I minimized most of the unnecessary columns to show what we are really need. See Figure 1.

Figure 1 Employee Table

In the following sample script, I illustrate one way to write a very simple Pivot Clause. See Figure 2, Simple Pivot Clause.

Figure 2 Simple Pivot Clause

I am using a vertical format in my script strictly for illustrative purposes so you can follow my code and bracketing structure as easily as possible. The results produce cells that show the total salaries summed under each department in one row. See Figure 3.

Figure 3 Pivot Clause output

Unpivot

In preparation for this portion, I created a view we will query so we can unpivot the data shown in Figure 3. Here is one way to write a script to unpivot the data. The method I chose to unpivot is in the following script. See Figure 4.

Figure 4 UnPivot Clause

The SQL script results are much different than what we created in the Pivot Clause earlier. Now we have a vertical output listing each department in its own row with their adjacent salary totals. For organizational purposes, I then ordered the data in descending salary order. The output is shown in Figure 5.

Figure 5 Unpivot Clause output.

Depending on the user needs for information, you can greatly simplify the user experience using the Pivot and UnPivot Clauses in SQL. I hope this helps your understanding of Pivot and UnPivot in SQL coding projects you are called on creating in the future.

Video Presentation

Insightfully yours,

Robert Majdak Sr., Co-Founder

Crystal Majdak, Co-Founder