ORACLE PIVOTING QUERIES IN TEAMSQL
One interesting feature in Oracle is the PIVOT operator, which is available from Oracle version 11g. This feature is frequently used in data warehouse environments to solve cross queries in few lines of SQL code. In this article, we will explain how to use the PIVOT clause in Oracle to create powerful cross queries using TeamSQL.
First of all, what are cross queries or pivot tables ?. A pivot table is a table of statistics that summarize the data of a more extensive table (such as from a database or a spreadsheet). This summary might include sums, averages, count or other statistics, which the pivot table groups together in a meaningful way. Pivot tables are a technique in data processing. They enable a person to arrange and rearrange (or “pivot”) statistics to draw attention to useful information.
The difference with a regular query is the way to show the results in the pivot query. We need to change the way we display the data from rows to columns. In other words, we need to convert the rows values into column values. Hence the term “pivot”.
In the next image, we can see the total amount of sales aggregated by channel_id and quarter in 2018 by quarter. This is our raw query result without any pivoting process.
As we can see on the previous image the value of channel_id appears duplicated for each quarter. However, in the next image we can see a better way to show the same results. We have one row per channel_id, and 4 columns, one per each quarter in 2018.
CAN WE SOLVE WITHOUT THE PIVOT CLAUSE?
Yes, we can; however, it is not straightforward. We can create a query to obtain the results in one row per channel_id, without using a PIVOT clause, this is a non-intuitive query, and hard to maintain because it has several duplicated parts of the code.
In the next TeamSQL print-screen we will show a query equivalent to the previous one regarding results, but using the PIVOT clause. Let’s see it
We can appreciate that the second query is simpler than the first one. Let’s see how the complexity will increase if we need columns for 12 quarters, instead of 4 quarters. In this case, we will need 12 case statements to calculate aggregates. Other difference is in the name of the columns; we have a column name composed by quarter name and a description of the amount calculated.
Let’s mention a foundational rule of SQL Best Practices, “All that you can solve in a single query must be solved in a single query”
Let’s continue by adding the column product_family to our query and try to aggregate the cumulative amounts using both columns: quarter and product_family. Let’s see how simple is our query, and at the same time, think how complex is the calculation we are doing to obtain this results.
Let’s continue adding new elements to our pivot query, let’s suppose we have a column storing the number of transactions we have each quarter. We can have two columns in our query, one for total amount sold, and other for a quantity of transactions. Let’s see in the next print screen of TeamSQL how simple is the query:
Last, but not least I like to leave an exercise for the reader. As we have a total amount and quantity of transactions, we can calculate the average amount per sold, which is a critical metric in all sales business. Can you build this query? Trust in your knowledge, and apply to do it in TeamSQL.