---
title: "PIVOT vs UNPIVOT in SQL: Top Differences Explained"
description: "The text explains how to use PIVOT and UNPIVOT in Oracle SQL to transform data between rows and columns for analysis and reporting."
url: https://srinimf.com/2023/05/13/how-to-convert-rows-to-columns-pivot-function/
date: 2023-05-13
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2023/05/laptop-with-stand.png?fit=1024%2C768&ssl=1
categories: ["SQL"]
tags: ["SQL PIVOT Example", "SQL UNPIVOT Example"]
type: post
lang: en
---

# 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](https://srinimf.com/2020/08/30/sql-common-table-expression-best-example/) `PIVOT` and `UNPIVOT` in Oracle SQL.

Related[PIVOT vs UNPIVOT: Must-Know Concepts for PySpark Developers](https://srinimf.com/2024/09/20/pivot-vs-unpivot-a-guide-for-pyspark-and-pandas-users/)

![PIVOT and UNPIVOT Examples](https://i0.wp.com/srinimf.com/wp-content/uploads/2023/05/pexels-photo-4270221.jpeg?resize=1880%2C1253&ssl=1)

## 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);
```

Related[Insert Into with Select SQL: How to Write SQL Query](https://srinimf.com/2023/01/05/how-to-write-insert-and-select-in-single-query/)

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.
```

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2023%2F05%2F13%2Fhow-to-convert-rows-to-columns-pivot-function%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2023%2F05%2Flaptop-with-stand.png%3Ffit%3D1024%252C768%26ssl%3D1&description=PIVOT%20vs%20UNPIVOT%20in%20SQL%3A%20Top%20Differences%20Explained)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2023/05/13/how-to-convert-rows-to-columns-pivot-function/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2023/05/13/how-to-convert-rows-to-columns-pivot-function/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2023/05/13/how-to-convert-rows-to-columns-pivot-function/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2023/05/13/how-to-convert-rows-to-columns-pivot-function/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20PIVOT%20vs%20UNPIVOT%20in%20SQL%3A%20Top%20Differences%20Explained&body=https%3A%2F%2Fsrinimf.com%2F2023%2F05%2F13%2Fhow-to-convert-rows-to-columns-pivot-function%2F&share=email)
-

### Like this:

Like Loading…