PIVOT and UNPIVOT in Oracle SQL

Key Concepts

PIVOT and UNPIVOT are powerful SQL operators in Oracle that allow you to transform data between rows and columns. Understanding these concepts is essential for manipulating and analyzing data effectively.

1. PIVOT

The PIVOT operator transforms rows into columns. It is particularly useful when you have a table where you want to display aggregated data across different categories as separate columns.

Example:

Consider a table named Sales with columns Year, Quarter, and Amount. Using PIVOT, you can transform the data to display each quarter's sales as separate columns:


        SELECT * FROM (
            SELECT Year, Quarter, Amount FROM Sales
        ) PIVOT (
            SUM(Amount) FOR Quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
        );

2. UNPIVOT

The UNPIVOT operator transforms columns into rows. It is useful when you have a table where you want to convert columnar data into a more normalized format.

Example:

Consider a table named SalesSummary with columns Year, Q1, Q2, Q3, and Q4. Using UNPIVOT, you can transform the data to display each quarter's sales as rows:


        SELECT Year, Quarter, Amount FROM SalesSummary
        UNPIVOT (
            Amount FOR Quarter IN (Q1, Q2, Q3, Q4)
        );

Detailed Explanation

1. PIVOT

The PIVOT operator works by aggregating data and then rotating the rows into columns. It requires a source query, an aggregation function, and a list of pivot columns.

Example:

Suppose you have a table Sales with the following data:


        Year | Quarter | Amount
        -----+---------+--------
        2021 | Q1      | 1000-2021 | Q2      | 1500-2021 | Q3      | 2000-2021 | Q4      | 2500

Using PIVOT, you can transform this into:


        Year | Q1  | Q2  | Q3  | Q4
        -----+-----+-----+-----+-----
        2021 | 1000| 1500| 2000| 2500

2. UNPIVOT

The UNPIVOT operator works by converting columns into rows. It requires a source query, a new column to hold the column names, and a new column to hold the values.

Example:

Suppose you have a table SalesSummary with the following data:


        Year | Q1  | Q2  | Q3  | Q4
        -----+-----+-----+-----+-----
        2021 | 1000| 1500| 2000| 2500

Using UNPIVOT, you can transform this into:


        Year | Quarter | Amount
        -----+---------+--------
        2021 | Q1      | 1000-2021 | Q2      | 1500-2021 | Q3      | 2000-2021 | Q4      | 2500

Examples and Analogies

Example 1: PIVOT

Imagine you have a list of sales figures for each quarter. Using PIVOT, you can transform this list into a summary table where each quarter's sales are displayed as separate columns.

Example 2: UNPIVOT

Consider a summary table where sales figures for each quarter are stored in separate columns. Using UNPIVOT, you can convert this summary table back into a list format, making it easier to analyze and manipulate.

Example 3: PIVOT and UNPIVOT Together

You can use PIVOT to summarize data and then use UNPIVOT to revert it back to its original format if needed. This flexibility allows you to manipulate data in various ways to suit your analysis needs.