Reshaping Tables
Pivoting rotates row values into columns, turning a tall table into a wide cross tab. Unpivoting does the reverse, folding many columns into rows. These reshape data without changing its meaning.
How Pivot Works
A pivot groups by some key and spreads a category column across new columns, picking an aggregate for each cell:
- Choose a column whose distinct values become the new column headers.
- Choose an aggregate, like SUM of sales, to fill each cell.
- Rows are grouped by the remaining key, such as month.
In standard SQL you can pivot with CASE inside aggregates, one CASE per target column. Some engines offer a dedicated PIVOT clause.
How Unpivot Works
Unpivot takes wide columns like jan, feb, and mar and produces rows with a month label and a value. Standard SQL does this with a UNION of selects or a cross join against a list of column names.
When To Use Each
Pivot for human readable reports and dashboards. Unpivot to normalize spreadsheet style data so it is easier to filter and aggregate.
Key idea
Pivoting rotates rows into columns for cross tab reports using CASE inside aggregates, and unpivoting folds wide columns back into tall normalized rows.