02 Nov 2010 Pivot Data in Standard Query Language (SQL)

Pivoting data is one of the most common techniques in BI used to present and manipulate data. Using an ETL or BI tool is usually a very simple solution; accomplishing it in SQL is more tedious.

In this article we will see two different ways to pivot data with SQL. The first one is the classical way, while the second one uses the PIVOT clause available in Oracle 11g.

Scenario

The goal is to get the data in the following table as a data source…

Figure 1: Source Data

… and pivot it to get the following results showing the amount by sales, persons and product:

Figure 2: Pivoted Data

Before starting, it is very important to be clear on the structure of a pivot table. The following image will help clarify this:

Figure 3: Pivot table structure showing the Rows, Columns and Measures areas

Pivot data in SQL (Classical Query)

The classical way of pivoting data consists of:

• Grouping the source data by the columns to be used, which is located in the rows area of the final pivot table
• Using CASE/DECODE to build the measure values in the measure area for each value in the columns area

So the SQL statement for the previous scenario is:

Pivot data in Oracle 11g SQL

If you are developing in the Oracle 11g Database, you could choose between the classical approach described above, using the new PIVOT clause available in Oracle 11g.

This SQL statement clause is embedded in the query between the FROM and WHERE clauses. Its syntax is the following:

PIVOT ( columns to be aggregated including the aggregation function

FOR columns to be pivoted

IN (values in the columns area and alias)

);

For each value in the IN clause, a new column will be created which contains the aggregated measure specified in the first part of the clause.

The query for the previous scenario would be the following:

Comparison Analysis

In both approaches, the developer needs to know the different values in advance. Any change in the available values may require a change in the SQL statement, if we want the new value to be shown in the columns area.

Using the PIVOT clause available in Oracle 11g, you will only need to add the value and the alias in the PIVOT clause and the alias in the SELECT clause, whereas in the classical approach you will need to rewrite the whole formula.

There is no “best” syntax to pivot data. Practice using both of these approaches and use the one you feel more comfortable with as long as your team is comfortable with that syntax. Take into account that some of your colleagues may be responsible for updating it in the future.