What is PIVOT?
Before PIVOT (Vertical)
| student | subject | score |
|---|
| John | Math | 90 |
| John | English | 85 |
| Mary | Math | 95 |
| Mary | English | 88 |
4 rows
After PIVOT (Horizontal)
| student | Math | English |
|---|
| John | 90 | 85 |
| Mary | 95 | 88 |
2 rows
PIVOT transforms rows into columns.
Simple analogy: Turning a vertical list into a horizontal table.
Before PIVOT (Rows)
Before PIVOT (Vertical)
| student | subject | score |
|---|
| John | Math | 90 |
| John | English | 85 |
| Mary | Math | 95 |
| Mary | English | 88 |
4 rows
After PIVOT (Horizontal)
| student | Math | English |
|---|
| John | 90 | 85 |
| Mary | 95 | 88 |
2 rows
| student | subject | score |
|---------|---------|-------|
| John | Math | 90 |
| John | English | 85 |
| Mary | Math | 95 |
| Mary | English | 88 |
After PIVOT (Columns)
Before PIVOT (Vertical)
| student | subject | score |
|---|
| John | Math | 90 |
| John | English | 85 |
| Mary | Math | 95 |
| Mary | English | 88 |
4 rows
After PIVOT (Horizontal)
| student | Math | English |
|---|
| John | 90 | 85 |
| Mary | 95 | 88 |
2 rows
| student | Math | English |
|---------|------|---------|
| John | 90 | 85 |
| Mary | 95 | 88 |
Subjects became columns!
PIVOT Example
Before PIVOT (Vertical)
| student | subject | score |
|---|
| John | Math | 90 |
| John | English | 85 |
| Mary | Math | 95 |
| Mary | English | 88 |
4 rows
After PIVOT (Horizontal)
| student | Math | English |
|---|
| John | 90 | 85 |
| Mary | 95 | 88 |
2 rows
SELECT *
FROM (
SELECT student, subject, score FROM grades
)
PIVOT (
MAX(score)
FOR subject IN ('Math', 'English', 'Science')
);
Note: Syntax varies by database (PostgreSQL uses crosstab, SQL Server uses PIVOT).
UNPIVOT - Opposite of PIVOT
Before PIVOT (Vertical)
| student | subject | score |
|---|
| John | Math | 90 |
| John | English | 85 |
| Mary | Math | 95 |
| Mary | English | 88 |
4 rows
After PIVOT (Horizontal)
| student | Math | English |
|---|
| John | 90 | 85 |
| Mary | 95 | 88 |
2 rows
Transforms columns back to rows.
Useful for normalizing data.
Real Example: Monthly Sales
Before PIVOT (Vertical)
| student | subject | score |
|---|
| John | Math | 90 |
| John | English | 85 |
| Mary | Math | 95 |
| Mary | English | 88 |
4 rows
After PIVOT (Horizontal)
| student | Math | English |
|---|
| John | 90 | 85 |
| Mary | 95 | 88 |
2 rows
Before PIVOT:
| product | month | sales |
|---------|-------|-------|
| Laptop | Jan | 100 |
| Laptop | Feb | 150 |
| Mouse | Jan | 200 |
After PIVOT:
| product | Jan | Feb |
|---------|-----|-----|
| Laptop | 100 | 150 |
| Mouse | 200 | 0 |
Summary
Before PIVOT (Vertical)
| student | subject | score |
|---|
| John | Math | 90 |
| John | English | 85 |
| Mary | Math | 95 |
| Mary | English | 88 |
4 rows
After PIVOT (Horizontal)
| student | Math | English |
|---|
| John | 90 | 85 |
| Mary | 95 | 88 |
2 rows
PIVOT = Rows to columns
UNPIVOT = Columns to rows
Makes data easier to read and analyze