Pivot Data in Standard Query Language (SQL)

.
Pivoting data is one of the most common techniques in BI to present and manipulate data. While doing this using an ETL or BI tool is usually very simple, doing it in SQL is a more tedious task.
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…

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…

ClearPeaks - Marketing - Blog - Pivot Data in SQL - Image 1.v1.0

Figure 1: Source Data


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

ClearPeaks - Marketing - Blog - Pivot Data in SQL - Image 2.v1.0

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:

ClearPeaks - Marketing - Blog - Pivot Data in SQL - Image 3.v1.0

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:

ClearPeaks - Marketing - Blog - Pivot Data in SQL - Image 4.v1.0

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:

ClearPeaks - Marketing - Blog - Pivot Data in SQL - Image 5.v1.0

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.

2 Responses to “Pivot Data in Standard Query Language (SQL)”

  1. Aditya Singh says:

    I am trying pivot method to implement in PHP but its giving some error.

    Table is clientdetails with 3 attributes (clientname,productname,quantity).

    <?php

    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("HR") or die(mysql_error());

    $sql=mysql_query("select clientname, 'Software' as s, 'Desktop' as d
    FROM (
    select clientname,productname,quantity
    FROM clientdetails)
    ps pivot (Sum(quantity) for productname in ('Software' ,'Desktop'))") or die("". mysql_error());

    Its showing error as

    "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pivot (Sum(quantity) for productname in ('Software' ,'Desktop'))' at line 5 ".

    Can someone help me in this?

    Thanks,

    Aditya Singh

  2. Shobha says:

    How would you do a Pivot for undefined number of rows/columns.

    Like we have a table that contains Customer/Accounts information in Banking domain. Each row will describe a Customer and associated Account information is stored in the columns. Each Customer may have more than 1 account, and the max number of accounts seen is 150. Each Customer row can store upto 50 Account numbers. So for a Customer with 150 accounts will have 3 rows in the table. Now there is a requirement from the Marketing team that they need data at Account level. In this case, how to do pivot of the Customer level data to Account level data.

Leave a Comment

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav