Pivot and unpivot concepts in SQL - Step-by-Step Execution

Concept Flow - Pivot and unpivot concepts

Start with raw table

↓

Choose columns to transform

↓

Pivot: Rows to Columns

↓

Aggregated summary

↓

Unpivot: Columns to Rows

↓

Normalized detailed table

Pivot turns row values into columns to summarize data. Unpivot reverses this, turning columns back into rows for detailed view.

Execution Sample

SQL

SELECT Year, Product, Sales FROM SalesData;
-- Pivot example:
SELECT Year, [A] AS ProductA, [B] AS ProductB FROM
(SELECT Year, Product, Sales FROM SalesData) src
PIVOT (SUM(Sales) FOR Product IN ([A], [B])) AS pvt;
-- Unpivot example:
SELECT Year, Product, Sales FROM
(SELECT Year, ProductA, ProductB FROM PivotedData) p
UNPIVOT (Sales FOR Product IN (ProductA, ProductB)) AS unpvt;

Shows raw sales data, then pivots product sales by year, then unpivots back to detailed rows.

Execution Table

Step	Action	Input Data	Transformation	Output Data
1	Start with raw data	[{Year:2020, Product:A, Sales:100}, {Year:2020, Product:B, Sales:150}, {Year:2021, Product:A, Sales:200}, {Year:2021, Product:B, Sales:250}]	None	Same as input
2	Pivot rows to columns	Raw data	Group by Year, create columns for Product A and B with SUM(Sales)	[{Year:2020, ProductA:100, ProductB:150}, {Year:2021, ProductA:200, ProductB:250}]
3	Unpivot columns to rows	Pivoted data	Convert ProductA and ProductB columns back to rows with Product name and Sales	[{Year:2020, Product:A, Sales:100}, {Year:2020, Product:B, Sales:150}, {Year:2021, Product:A, Sales:200}, {Year:2021, Product:B, Sales:250}]
4	End	Unpivoted data	No further transformation	Same as step 1 data

💡 Transformation complete: data returned to original detailed form after pivot and unpivot

Variable Tracker

Variable	Start	After Pivot	After Unpivot	Final
DataSet	Raw rows with Year, Product, Sales	Rows grouped by Year with Product columns and summed Sales	Rows with Year, Product, Sales restored	Same as Start

Key Moments - 3 Insights

Why does pivot create new columns instead of rows?

Pivot aggregates data by grouping rows and turning unique values from one column into new columns, as shown in execution_table step 2.

How does unpivot restore the original row format?

Unpivot takes multiple columns and converts them back into rows with a column indicating the original column name, as seen in execution_table step 3.

Can pivot and unpivot be used on any data?

They require structured data where pivot columns and unpivot columns are known; otherwise, the transformation won't work correctly.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output data after the pivot step?

A [{Year:2020, Product:A, Sales:100}, {Year:2020, Product:B, Sales:150}]

B [{Year:2020, ProductA:100, ProductB:150}, {Year:2021, ProductA:200, ProductB:250}]

C [{Year:2020, Product:A, Sales:250}, {Year:2021, Product:B, Sales:450}]

D [{Year:2020, ProductA:250, ProductB:400}]

💡 Hint

Check execution_table row 2 under Output Data for the pivot result.

At which step does the data return to its original detailed row format?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at execution_table rows 3 and 4; unpivot happens at step 3.

If the pivot step used MAX instead of SUM, how would the output change?

A It would show the maximum sales per product per year instead of total sales.

B It would show the minimum sales per product per year.

C It would not change the output.

D It would cause an error.

💡 Hint

Pivot aggregates data; changing SUM to MAX changes aggregation behavior as in execution_table step 2.

Concept Snapshot

Pivot turns row values into columns to summarize data.
Unpivot reverses this, turning columns back into rows.
Use PIVOT with aggregation functions like SUM.
Use UNPIVOT to normalize columns into rows.
Both require knowing which columns to transform.

Full Transcript

Pivot and unpivot are SQL techniques to reshape data. Pivot converts row values into columns, often summarizing data by grouping and aggregating. Unpivot reverses this process, turning columns back into rows for detailed analysis. The execution flow starts with raw data, pivots it to a summarized form, then unpivots it back to the original detailed rows. Variables like the dataset change shape accordingly. Key moments include understanding why pivot creates columns and how unpivot restores rows. The visual quiz tests understanding of these steps and aggregation effects.