Pivot & Unpivot | SQL Tutorial

What is Pivoting?

Pivoting rotates a table-valued expression by expanding the unique values from one column into multiple columns. It's often used for generating reports.

[!NOTE] Database support for PIVOT syntax varies (SQL Server/Oracle support it natively). In MySQL/PostgreSQL, we typically use CASE statements (Conditional Aggregation).

Example: Sales by Month (Row to Column)

Source Data: | Month | Product | Sales | | :--- | :--- | :--- | | Jan | A | 100 | | Jan | B | 150 | | Feb | A | 200 |

Pivot Goal: | Product | Jan | Feb | | :--- | :--- | :--- | | A | 100 | 200 | | B | 150 | 0 |

Conditional Aggregation (Standard SQL)

SELECT Product,
    SUM(CASE WHEN Month = 'Jan' THEN Sales ELSE 0 END) AS Jan,
    SUM(CASE WHEN Month = 'Feb' THEN Sales ELSE 0 END) AS Feb
FROM SalesData
GROUP BY Product;

sql-pivot-case

Manual Pivot

Problem Statement

Pivot the Sales table to show total sales for 'Q1' and 'Q2' as columns by Region.

What is Unpivoting?

The reverse operation: converting columns back into rows. Often done using UNION ALL.

SELECT Product, 'Jan' as Month, Jan_Sales as Sales FROM PivotTable
UNION ALL
SELECT Product, 'Feb' as Month, Feb_Sales as Sales FROM PivotTable;