Business Intelligence – Oracle

Archive for September 5th, 2007

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

Posted in All Posts, Oracle 11g | 1 Comment »