Chapter 07 · 11 min · Basic
Long or wide
The free tutorial already shows you how to pivot: a GROUP BY with one aggregate per column, or FILTER if you like it tidy. That is the mechanic, and it is a five-minute read.
This chapter is about the part nobody teaches: which shape the question actually needs, and what the wrong one costs you three weeks later. The same twelve numbers can sit in a table with one row per region per month, or in a grid with one row per region and one column per month. Neither is more correct. One of them breaks the day a new region opens, and one of them nobody can read.
The two shapes also disagree about something the long table cannot even express — the difference between a zero and a cell that never existed.
The dataset
Twelve rows of monthly revenue, one per region per month. Four regions over four months — but not sixteen rows: Johor is missing a month it should have, and Sabah only opened in April. Both gaps are deliberate, and telling them apart is the middle of this chapter.
Schema
| region | text |
| month | date |
| revenue_myr | numeric(10,2) |
Example data
The same numbers, two shapes
Twelve rows. One row per region per month, which is the shape a database naturally accumulates: something happened, a row got written.
Read it and nothing is wrong with it. It is complete, it is correct, and nobody has ever asked for a report that looks like this.
What they ask for is a grid — regions down the side, months across the top — and the reflex is to reach for the pivot immediately. Reaching for it immediately is the mistake this chapter is about. The shape is not a cosmetic choice made at the end; it decides what the next question costs.
Four regions and four months. Will this return sixteen rows?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. Both data-science tracks are the paid half — this one is the level-up: grain, reshaping, change over time, and whether the number really moved.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Wide is a promise that the columns are fixed
- 03The pivot invents the cells that were never there
- 04Long survives the next question
- 05Choose the shape by who reads it
- 06Now do it where the category list is not four