PIVOT vs UNPIVOT in SQL: Top Differences Explained

Let’s go through an example using a table with some data. Then, I’ll show how to use PIVOT and UNPIVOT in Oracle SQL.

Example Table Data

Here is a table sales with columns month, product, and revenue:

month	product	revenue
Jan	Product A	100
Jan	Product B	150
Jan	Product C	200
Feb	Product A	110
Feb	Product B	160
Feb	Product C	210

Using PIVOT

The PIVOT operation transforms rows into columns. For example, to pivot the product values into columns with aggregated revenue for each month:

SELECT *
FROM (
    SELECT month, product, revenue
    FROM sales
)
PIVOT (
    SUM(revenue) 
    FOR product IN (
        'Product A' AS "Product_A", 
        'Product B' AS "Product_B", 
        'Product C' AS "Product_C")
);

Result of PIVOT

The result will look like this, with product values turned into columns:

month	Product_A	Product_B	Product_C
Jan	100	150	200
Feb	110	160	210

Using UNPIVOT

The UNPIVOT operation transforms columns into rows. For example, if you have a table where products are columns, and you want to convert it back to a long format:

SELECT month, product, revenue
FROM (
    SELECT month, Product_A, Product_B, Product_C
    FROM pivoted_sales
)
UNPIVOT (
    revenue FOR product IN (
            Product_A AS 'Product A', 
            Product_B AS 'Product B', 
            Product_C AS 'Product C')
);

Example Pivoted Table Data

Assuming you have a pivoted table pivoted_sales with columns:

month	Product_A	Product_B	Product_C
Jan	100	150	200
Feb	110	160	210

Result of UNPIVOT

The result will look like this, with product columns turned into rows:

month	product	revenue
Jan	Product A	100
Jan	Product B	150
Jan	Product C	200
Feb	Product A	110
Feb	Product B	160
Feb	Product C	210

Summary

• PIVOT: Converts rows into columns, typically used for aggregation and summarizing data across multiple dimensions.
• UNPIVOT: Converts columns into rows, often used to normalize or denormalize data.

Both operations are useful for transforming data to better suit your analysis or reporting needs.

Executed the Queries in Oracle

-- CREATE TABLE sales (
--     month VARCHAR2(3),
--     product VARCHAR2(20),
--     revenue NUMBER(10,2)
-- );


-- INSERT INTO sales (month, product, revenue) VALUES ('Jan', 'Product A', 100);
-- INSERT INTO sales (month, product, revenue) VALUES ('Jan', 'Product B', 150);
-- INSERT INTO sales (month, product, revenue) VALUES ('Jan', 'Product C', 200);
-- INSERT INTO sales (month, product, revenue) VALUES ('Feb', 'Product A', 110);
-- INSERT INTO sales (month, product, revenue) VALUES ('Feb', 'Product B', 160);
-- INSERT INTO sales (month, product, revenue) VALUES ('Feb', 'Product C', 210);

PIVOT

-- I have written pivoted data in the cte, later to use the same for Unpivot
WITH cte AS (
    SELECT *
    FROM (
        SELECT month, product, revenue
        FROM sales
    )
    PIVOT (
        SUM(revenue) 
        FOR product IN ('Product A' AS "Product_A", 'Product B' AS "Product_B", 'Product C' AS "Product_C")
    )
)
SELECT * FROM cte;

-- Output
MONTH	Product_A	Product_B	Product_C
Jan	100	        150	        200
Feb	110	        160	        210
Download CSV
2 rows selected.

UNPIVOT

WITH cte AS (
    SELECT *
    FROM (
        SELECT month, product, revenue
        FROM sales
    )
    PIVOT (
        SUM(revenue) 
        FOR product IN ('Product A' AS "Product_A", 'Product B' AS "Product_B", 'Product C' AS "Product_C")
    )
)
-- Selecting data from the cte and applying UNPIVOT to return to the original structure
SELECT month, product, revenue
FROM (
    SELECT month, "Product_A", "Product_B", "Product_C"  -- Ensuring case-sensitive column names
    FROM cte
)
UNPIVOT (
    revenue FOR product IN ("Product_A" AS 'Product A', "Product_B" AS 'Product B', "Product_C" AS 'Product C')
);

-- Output
MONTH	PRODUCT	REVENUE
Jan	Product A	100
Jan	Product B	150
Jan	Product C	200
Feb	Product A	110
Feb	Product B	160
Feb	Product C	210
Download CSV
6 rows selected.