Two Shapes, Two Audiences
The same dataset can be arranged in two fundamentally different ways, and almost every reporting frustration comes from having the wrong one for the task at hand.
Wide format puts one column per period or category. A revenue table with columns Jan, Feb, Mar through Dec. A human reads this instantly: rows are entities, columns are time, the eye scans across.
Long format puts the period in a column as a value. Three columns — entity, month, revenue — and twelve rows per entity. A human finds this tedious. A database finds it obvious.
Neither is correct in the abstract. The mistake is picking one by accident and then fighting it for the rest of the project. Recognising which shape you have, and being able to move between them, turns a category of hard problems into easy ones.
How to Tell Which Shape You Are Looking At
There is a reliable test: read the column headers and ask whether any of them is data.
If your headers include dates, years, quarters, product names, region names, or status values, those are data values that have been promoted into schema. That is wide format. If every header names an attribute — customer, month, amount, status — and the values live only in cells, that is long format.
This test matters because the consequences of wide format are structural, not cosmetic:
- New periods require schema changes. Next January needs a new column, which means a migration, and every query that enumerated the months needs updating.
- Aggregating across periods is painful. A total for the year means naming twelve columns. A total for a rolling window means naming a different twelve.
- Filtering by period is impossible in a WHERE clause. You cannot filter on a column name. You can only select fewer columns, which is a different operation.
- Nulls become ambiguous. A null in the Nov column might mean zero revenue or might mean November has not happened yet, and there is nowhere to record which.
- Indexes cannot help. There is no single column holding periods, so there is nothing to index for period-based access.
Which Direction You Need
| Task | Shape You Want | Why |
|---|---|---|
| Storing data in a database | Long | Stable schema, indexable, extends without migration. |
| Aggregating or filtering by period | Long | Period is a value, so GROUP BY and WHERE work normally. |
| Feeding a chart or BI tool | Long | Most visualisation libraries expect one observation per row. |
| A report someone reads on screen | Wide | Comparing across periods on one row is much easier visually. |
| Exporting for a spreadsheet user | Wide | It is what they expect, and they will pivot it themselves otherwise. |
| Joining to other datasets | Long | Join keys must be values, not column names. |
The general rule that falls out of this: store long, present wide. Keep the canonical data in long format and pivot only at the last step, for display. Pivoting early and storing the result is how the structural problems above become permanent.
Unpivoting: Wide to Long
This is the direction you need when receiving a report-shaped export that has to go into a database. In SQL, the portable approach is a union of one select per source column.
-- Portable across dialects
SELECT customer, '2026-01' AS month, jan AS revenue FROM wide_revenue
UNION ALL
SELECT customer, '2026-02' AS month, feb AS revenue FROM wide_revenue
UNION ALL
SELECT customer, '2026-03' AS month, mar AS revenue FROM wide_revenue;It is verbose but works everywhere and is easy to verify. PostgreSQL can do this more compactly with a lateral join over a values list; SQL Server has UNPIVOT; MySQL has no dedicated syntax, so the union form is the practical choice there.
Two things to decide before writing it. First, how to handle nulls: in wide format a null often means "no data yet", and carrying those rows into long format creates rows that claim an observation exists. Usually you want to filter them out. Second, how to parse the period: a column named "jan" carries no year, so the year has to come from context, and getting that wrong is a silent error.
In practice, the reshaping is often easier before the data reaches SQL at all. The table editor in Develop Box Utilities has a transpose operation that flips rows and columns directly, with undo, which for a one-off report export is faster than writing a twelve-way union and easier to check by eye.
Pivoting: Long to Wide
This is the presentation direction. The standard portable technique is conditional aggregation.
SELECT
customer,
SUM(CASE WHEN month = '2026-01' THEN revenue END) AS jan,
SUM(CASE WHEN month = '2026-02' THEN revenue END) AS feb,
SUM(CASE WHEN month = '2026-03' THEN revenue END) AS mar,
SUM(revenue) AS total
FROM long_revenue
WHERE month BETWEEN '2026-01' AND '2026-03'
GROUP BY customer
ORDER BY customer;The aggregate around the CASE is required, not decorative: without it the expression is not aggregated and the group by fails. Omitting the ELSE yields null rather than zero for missing combinations, which is usually what you want since it distinguishes "no data" from "zero".
The fundamental limitation is that SQL requires a fixed column list. You must know the columns when you write the query. If the set of periods varies, you have three options: generate the SQL from a query that lists the distinct values, use the dialect's dedicated pivot feature where available, or pivot in the presentation layer instead. The third is usually cleanest, because it keeps the database returning stable long-format results.
The Mistakes Worth Avoiding
- Storing the pivoted result. Pivot for display, then discard. Persisting a wide table means every new period is a migration, and you will do that migration under time pressure.
- Unpivoting nulls without thinking. Turning an empty Nov cell into a row asserting November revenue was null is different from having no November row. Decide which you mean.
- Losing the year when column names are month abbreviations. Reconstruct the full period explicitly, from a documented source, not from assumption.
- Pivoting on unbounded categories. If the distinct values could number in the hundreds, a pivot produces an unusable table. Cap it, or aggregate the long tail into an "other" bucket.
- Forgetting that transposition changes the header row. After a transpose, what was your first column becomes the header. Check that the result still has a meaningful header before generating SQL from it.
- Double counting after unpivot. If the wide table contained a total column alongside the monthly ones, unpivoting includes the total as another period. Drop derived columns before reshaping.
Frequently Asked Questions
Is long format always the right way to store data?
For transactional and analytical storage, nearly always, because the schema stays stable as new categories appear. The exception is a genuinely fixed, small set of attributes that will never grow — a table with separate columns for latitude and longitude should not be unpivoted into a coordinate type and value. The test is whether the set of columns can grow over time.
How do I pivot when I do not know the columns in advance?
Standard SQL cannot, because the result shape must be known at parse time. Either query the distinct values first and generate the pivot SQL from that list, or return long format and pivot in the application or BI layer. The second option is usually better: it keeps the query stable and moves a presentation concern to the presentation layer.
Is transposing the same as unpivoting?
No, though they are often confused. Transposing swaps the row and column axes wholesale, so a 10-by-4 grid becomes 4-by-10. Unpivoting converts a set of columns into two columns of name and value, so a 10-by-4 grid becomes roughly 30-by-3. Transposition is a useful step when a report was laid out sideways; unpivoting is what you need to reach long format.
Conclusion: Store Long, Present Wide
Most reporting pain comes from a dataset being in the shape optimised for the other audience. Wide tables resist querying because they encode data in the schema; long tables resist reading because humans compare across, not down.
Keep the stored form long so the schema never has to change when a new period arrives, and pivot at the last possible moment for display. When you receive a wide export, unpivot it deliberately, being explicit about nulls, periods, and any derived columns that must be dropped first.