Systems Engineering and RDBMS

Archive for December 3rd, 2007

Oracle 11g: Cross-tab report using PIVOT and UNPIVOT operator

Posted by decipherinfosys on December 3, 2007

Microsoft introduced the PIVOT and UNPIVOT operator in SQL Server 2005 to generate cross tab reports i.e. to convert rows into columns and columns into rows. You can read more on that in our whitepaper. We have also covered the traditional way of pivoting the data for prior version of SQL Server and for other databases in one of our previous blog post.

In 11g Oracle introduced these two operators to convert rows to columns and vice versa. We will cover the basic flavor of PIVOT and UNPIVOT operators in the blog post. We are using the same examples as used in above mentioned blog post but for easier readability we are mentioning it here again. Create two tables to demonstrate the basic PIVOT and UNPIVOT functionality and populate it with data.

CREATE TABLE PRODUCT_COL
(
PRODUCT_ID NUMBER(9) NOT NULL,
PRODUCT_DESC VARCHAR2(50),
Q1_SALES_AMT NUMBER(13,2) DEFAULT 0 NOT NULL,
Q2_SALES_AMT NUMBER(13,2) DEFAULT 0 NOT NULL,
Q3_SALES_AMT NUMBER(13,2) DEFAULT 0 NOT NULL,
Q4_SALES_AMT NUMBER(13,2) DEFAULT 0 NOT NULL,
CONSTRAINT PK_PRODUCT_COL PRIMARY KEY(PRODUCT_ROW_ID,PRODUCT_ID)
)
/

INSERT INTO PRODUCT_COL (PRODUCT_ID,PRODUCT_DESC,Q1_SALES_AMT,Q2_SALES_AMT,Q3_SALES_AMT,Q4_SALES_AMT)
VALUES(1,’Sharpie’,130,215,190,300)
/

INSERT INTO PRODUCT_COL (PRODUCT_ID,PRODUCT_DESC,Q1_SALES_AMT,Q2_SALES_AMT,Q3_SALES_AMT,Q4_SALES_AMT)
VALUES(2,’Pencils’,2100,918,3280,1315)
/

CREATE TABLE PRODUCT_ROW
(
PRODUCT_ROW_ID NUMBER(9) NOT NULL,
PRODUCT_ID NUMBER(9) NOT NULL,
PRODUCT_DESC VARCHAR2(50),
Quarter_ID NUMBER(3) DEFAULT 0 NOT NULL,
Quarter_Amt NUMBER(13,2) DEFAULT 0 NOT NULL,
CONSTRAINT PK_PRODUCT_ROW PRIMARY KEY(PRODUCT_ROW_ID)
)
/

INSERT INTO PRODUCT_ROW(PRODUCT_ROW_ID,PRODUCT_ID,PRODUCT_DESC,Quarter_ID, Quarter_Amt)
VALUES(1,1, ‘Sharpie’,1, 130)
/
INSERT INTO PRODUCT_ROW(PRODUCT_ROW_ID,PRODUCT_ID,PRODUCT_DESC,Quarter_ID, Quarter_Amt)
VALUES(2,1, ‘Sharpie’,2, 215)
/
INSERT INTO PRODUCT_ROW(PRODUCT_ROW_ID,PRODUCT_ID,PRODUCT_DESC,Quarter_ID, Quarter_Amt)
VALUES(3,1, ‘Sharpie’,3, 190)
/
INSERT INTO PRODUCT_ROW(PRODUCT_ROW_ID,PRODUCT_ID,PRODUCT_DESC,Quarter_ID, Quarter_Amt)
VALUES(4,1, ‘Sharpie’,4, 300)
/
INSERT INTO PRODUCT_ROW(PRODUCT_ROW_ID,PRODUCT_ID,PRODUCT_DESC,Quarter_ID,Quarter_Amt)
VALUES(5,2,’Pencils’,1,2100)
/
INSERT INTO PRODUCT_ROW(PRODUCT_ROW_ID,PRODUCT_ID,PRODUCT_DESC,Quarter_ID,Quarter_Amt)
VALUES(6,2,’Pencils’,2,918)
/
INSERT INTO PRODUCT_ROW(PRODUCT_ROW_ID,PRODUCT_ID,PRODUCT_DESC,Quarter_ID,Quarter_Amt)
VALUES(7,2,’Pencils’,3,3280)
/
INSERT INTO PRODUCT_ROW(PRODUCT_ROW_ID,PRODUCT_ID,PRODUCT_DESC,Quarter_ID,Quarter_Amt)
VALUES(8,2,’Pencils’,4,1315)
/

We will start with PIVOT operator to convert row data into columns in a single row. Execute following SQL.

SELECT *
FROM
(
SELECT PRODUCT_DESC, QUARTER_ID, QUARTER_AMT FROM PRODUCT_ROW
)
PIVOT
(MAX(QUARTER_AMT) FOR QUARTER_ID IN (1 AS QTR1, 2 AS QTR2, 3 AS QTR3, 4 AS QTR4))
/

Pivot clause computes the aggregation function specified in the beginning. Use of aggregate function requires group by clause but we don’t need to specify one. PIVOT operator internally performs group by for the columns not specified in the PIVOT clause (product_desc) along with set of values used in ‘IN’ clause (quarter_id [1,2,3,4]). Following is the output of the query.

PRODUCT_DESC               QTR1       QTR2       QTR3       QTR4
 -------------------- ---------- ---------- ---------- ----------
 Sharpie                     130        215        190        300
 Pencils                    2100        918       3280       1315

Similarly UNPIVOT clause converts columns into rows. This is useful when we have to display column data into multiple rows as shown in example. Run following query.

SELECT *
FROM
(SELECT PRODUCT_DESC, Q1_SALES_AMT, Q2_SALES_AMT, Q3_SALES_AMT, Q4_SALES_AMT
FROM PRODUCT_COL
)
UNPIVOT
(Sales_AMt FOR QUARTER_NO IN (Q1_SALES_AMT,Q2_SALES_AMT,Q3_SALES_AMT,Q4_SALES_AMT))
ORDER BY PRODUCT_DESC, QUARTER_NO
/

In our example, we are converting quarterly sales data into an individual row. ‘FOR’ column specifies the name of each output column name which holds the data value. Following output of the query will make it more clear.

PRODUCT_DESC         QUARTER_NO    SALES_AMT
 -------------------- ------------ ----------
 Pencils              Q1_SALES_AMT       2100
 Pencils              Q2_SALES_AMT        918
 Pencils              Q3_SALES_AMT       3280
 Pencils              Q4_SALES_AMT       1315
 Sharpie              Q1_SALES_AMT        130
 Sharpie              Q2_SALES_AMT        215
 Sharpie              Q3_SALES_AMT        190
 Sharpie              Q4_SALES_AMT        300

Here SALES_AMT column actually holds the value of our quarterly sales and actual columns (Q1_SALES_AMT, Q2_SALES_AMT etc) are displayed as value of ‘FOR’ column QUARTER_NO.

Moreover UNPIVOT operator can also include or exclude null values based on the INCLUDE | EXCLUDE NULL clause. Also since we are unpivoting the column data into rows, we need to make sure that datatypes of all the value columns should be similar.

Posted in Oracle | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 80 other followers