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.
