Business Intelligence – Oracle

Oracle 11g – PIVOT and UNPIVOT

Posted by Venkatakrishnan J on September 5, 2007

If you had noticed, one of the most important new features of Oracle Database 11g from a SQL perspective is the introduction of PIVOT and UNPIVOT operators into SQL constructs. So, now one can have cross tab views, that we are so used to in reporting tools (like OBI EE), from sqlplus. Also, it would really be great if the reporting tools can take advantage of this construct while building the queries. I will demonstrate PIVOT and UNPIVOT with a small example here. The following picture shows the output of a simple crosstab from OBI EE.

      

What this crosstab does is, it analyzes the amount_sold (Sales table of the SH schema) across 2 dimensions countries and channels. Lets see how a normal query would have been to get this data in 10g or before (simple unpivoted query).

SELECT COUNTRY_NAME COUNTRY, CHANNEL_NAME CHANNEL, SUM(AMOUNT) AS AMOUNT
FROM
SALES A,
GEOGRAPHY B,
CHANNELS C
WHERE
A.GEOGRAPHY = B.DIMENSION_KEY AND
A.CHANNELS = C.DIMENSION_KEY
GROUP BY
COUNTRY_NAME, CHANNEL_NAME

The result of the above query would look something like this

      

Now, our aim is to build a cross tab out of this. Lets try creating a crosstab using the PIVOT operator directly on the above aggregate query. But before that let me give the syntax of the PIVOT operator (taken directly from the docs) here.

Syntax:

SELECT ….
FROM

PIVOT
(
aggregate-function()
FOR IN (, ,…, )
) AS
WHERE …..Here, table-expr would be our aggregate query that we specified above. Aggregate-function would require the measure which we would like to aggregate (in our case AMOUNT). pivot-column is the column that we would like to pivot (pivot the rows into columns). Alias is optional. Now lets build our query.SELECT *
FROM ( SELECT COUNTRY_NAME COUNTRY, CHANNEL_NAME CHANNEL, SUM(AMOUNT) AS AMOUNT
FROM
SALES A,
GEOGRAPHY B,
CHANNELS C
WHERE
A.GEOGRAPHY = B.DIMENSION_KEY AND
A.CHANNELS = C.DIMENSION_KEY
GROUP BY
COUNTRY_NAME, CHANNEL_NAME ) A
PIVOT
(
SUM(AMOUNT)
FOR CHANNEL IN (‘Direct Sales’, ‘Partners’, ‘Internet’)
)
ORDER BY COUNTRY      As you see above, we have pivoted the first query into a crosstab. To unpivot this, we would have to create a view on top of the above query so that we can unpivot that view.

CREATE OR REPLACE VIEW
PIVOT_AMOUNT_VW
AS
SELECT *
FROM
(
SELECT COUNTRY_NAME COUNTRY, CHANNEL_NAME CHANNEL, SUM(AMOUNT) AS AMOUNT
FROM
SALES A,
GEOGRAPHY B,
CHANNELS C
WHERE
A.GEOGRAPHY = B.DIMENSION_KEY AND
A.CHANNELS = C.DIMENSION_KEY
GROUP BY
COUNTRY_NAME, CHANNEL_NAME) A
PIVOT
(
SUM(AMOUNT)
FOR CHANNEL IN (‘Direct Sales’ AS DS, ‘Partners’ AS PARTNER, ‘Internet’ AS INTERNET)
)
ORDER BY COUNTRY

         

Lets unpivot the above view.

SELECT *
FROM PIVOT_AMOUNT_VW
UNPIVOT INCLUDE NULLS
(
AMOUNT
FOR CHANNEL IN (DS, PARTNER, INTERNET)
)
ORDER BY COUNTRY

      

Quite an interesting new feature considering the fact that pivoting is an operation that is done in almost all customer scenarios. There are a number of options for PIVOT and UNPIVOT. They can be obtained from the documentation here

About these ads

One Response to “Oracle 11g – PIVOT and UNPIVOT”

  1. martin said

    Hello, i was hoping you could help, i want the following select statement to be presented in a pivot table, and the output to be present as below:

    SELECT BGROUP, REFNO, SEQNO, PP02D, PP05P
    FROM PRESERVED_PENSIONER_HISTORY
    WHERE REFNO IN (’0084941′,’0079500′,
    ’0082833′,’0094388′,’0063814′,’0081368′)

    OUTPUT:
    REFNO | PP02D PP02D PP02D
    ———————————-
    0084941 | PP05P PP05P PP05P
    0079500 | PP05P PP05P PP05P
    0082833 | PP05P PP05P PP05P
    ETC |

    PP02D = VARIOUS DATES
    PP05P = VARIOUS VALUES FOR THOSE DATES

    Your help would be appreciated!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: