What is pivot and unpivot?

PIVOT and UNPIVOT are relational operators in SQL used for transforming data from rows to columns (PIVOT) and from columns to rows (UNPIVOT), respectively. They are particularly useful for data analysis and reporting, allowing for easier visualization and manipulation of summary data.

What is PIVOT?

The PIVOT operator transforms rows into columns, aggregating data from a row-based format into a columnar format. It rotates a table-valued expression by turning the unique values from one column into multiple columns, and performs aggregations where required. This operation makes it easier to compare and analyze data across different categories.

A common use case for PIVOT is when you have summary data stored in rows (e.g., sales figures per month for different years) and you want to display months as columns to easily compare sales across different periods. This transforms a 'tall' table into a 'wide' table, making reports more readable and concise.

```sql
-- Sample Data (Sales)
CREATE TABLE SalesData (
    SaleYear INT,
    SaleMonth VARCHAR(3),
    SaleAmount DECIMAL(10, 2)
);

INSERT INTO SalesData (SaleYear, SaleMonth, SaleAmount) VALUES
(2023, 'Jan', 100.00),
(2023, 'Feb', 150.00),
(2023, 'Mar', 120.00),
(2024, 'Jan', 110.00),
(2024, 'Feb', 160.00),
(2024, 'Mar', 130.00);

-- PIVOT Example
SELECT SaleYear, Jan, Feb, Mar
FROM (
    SELECT SaleYear, SaleMonth, SaleAmount
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(SaleAmount) -- The aggregate function to apply to the value column
    FOR SaleMonth IN ([Jan], [Feb], [Mar]) -- The column to pivot and the new column names
) AS PivotTable;
/*
-- Result:
SaleYear | Jan     | Feb     | Mar
---------|---------|---------|---------
2023     | 100.00  | 150.00  | 120.00
2024     | 110.00  | 160.00  | 130.00
*/
```

What is UNPIVOT?

The UNPIVOT operator performs the opposite operation of PIVOT. It transforms columns into rows, taking a column-based table and converting it back into a more normalized, row-based format. It essentially 'flattens' the data, making it 'tall' rather than 'wide'.

UNPIVOT is useful when you have data where different categories or attributes are stored as separate columns (e.g., monthly sales figures stored in individual columns like 'JanSales', 'FebSales'). To perform calculations or aggregations across these categories, it's often easier to first unpivot them into a single column, making each category a row. This is particularly useful for data warehousing and analysis when a consistent schema is required.

```sql
-- Sample Data (Pivoted Sales Data - often result of a PIVOT)
CREATE TABLE PivotedSalesData (
    SaleYear INT,
    Jan DECIMAL(10, 2),
    Feb DECIMAL(10, 2),
    Mar DECIMAL(10, 2)
);

INSERT INTO PivotedSalesData (SaleYear, Jan, Feb, Mar) VALUES
(2023, 100.00, 150.00, 120.00),
(2024, 110.00, 160.00, 130.00);

-- UNPIVOT Example
SELECT SaleYear, SaleMonth, SaleAmount
FROM PivotedSalesData
UNPIVOT (
    SaleAmount FOR SaleMonth IN (Jan, Feb, Mar) -- (value_column FOR pivot_column IN (source_columns))
) AS UnpivotTable;
/*
-- Result:
SaleYear | SaleMonth | SaleAmount
---------|-----------|-----------
2023     | Jan       | 100.00
2023     | Feb       | 150.00
2023     | Mar       | 120.00
2024     | Jan       | 110.00
2024     | Feb       | 160.00
2024     | Mar       | 130.00
*/
```

Key Differences and Use Cases

The fundamental difference between PIVOT and UNPIVOT lies in their direction of data transformation: PIVOT converts rows to columns (creating a 'wide' format), while UNPIVOT converts columns to rows (creating a 'tall' or more normalized format). They are inverse operations, often used in conjunction or to prepare data for different types of analysis or reporting tools.

• PIVOT Use Cases:
• Generating cross-tabulation reports for easier comparison.
• Displaying aggregated data with categories as columns (e.g., sales by product by quarter).
• Summarizing data for business intelligence dashboards where a wide format is preferred.
• UNPIVOT Use Cases:
• Normalizing 'wide' tables into a 'tall' or more normalized format for database design.
• Preparing data for statistical analysis where variables should be in a single column.
• Consolidating data from multiple columns into a single column for easier filtering or aggregation.
• Reversing the effect of a PIVOT operation, returning data to its original or a more normalized structure.