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
Transform rows into columns.
Before and After
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
Tables show transformation below.
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')
);UNPIVOT - Opposite
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
Columns back to rows. Useful for normalizing data.
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 become columns
- UNPIVOT: Columns become rows
- Syntax varies by database