Lesson 13 of 15 · Lab
Wide vs long data: pivot and unpivot
Long data has one row per observation, and wide data spreads one column's values across several columns. Pivoting turns long into wide, and unpivoting turns wide into long. Use wide tables for reading, and long tables for charts and further calculation.
Store A with 10 in January and 20 in February is two rows when long and one row with Jan and Feb columns when wide. In Strata, Oplema's browser app for data prep, Cross Tab pivots and Transpose unpivots.
This lab reshapes the net value of fictional Vandelay Coffee Supply by region and month, starting long.
| region | month | net |
|---|---|---|
| North | 2026-08 | 2,922 |
| South | 2026-08 | 1,260 |
| North | 2026-09 | 900 |
| South | 2026-09 | 3,780 |
| North | 2026-07 | 1,756 |
| South | 2026-07 | 860 |
Which month gave South its lead?
September, though you had to hunt for it across rows. Side by side, it stands out.
Pivot months into columns, in order
- Cross Tab is selected in this live Strata flow, turning
monthinto columns. The preview has two rows, one per region, with the months out of order. Columns follow the order the months first appear in the data. - Select the Select block. Under Columns, it lists them the same way.
- Select the ▲ beside
2026-07twice, so it sits just belowregion. - Read the preview. The columns should run July, August, September, and each region now reads left to right.
- Select Summarize, the last block. It checks that two rows and the September total came through.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
The values to expect
North 1,756, 2,922, 900. South 860, 1,260, 3,780. september reads 4,680.
Read wide, calculate long
North took €900 in September and South took €3,780. Laid out as columns, that gap shows up at once, and it says more than the quarter's totals did.
A chart or a further Summarize wants the long shape, where a new month is just new rows. In wide form it's a new column, and every later step that names columns needs attention, including this Select. Neither shape changes a number, so totals before and after have to match.
Common reshaping mistakes
A pivot is reversible only when each region and month pair has one value. Several rows for one pair must be combined, by summing for example, and unpivoting then returns the sum and not the original rows. Here, First is safe because each pair is unique.
Watch for two other problems. Pairs with no data become empty cells, which aren't zeros. And a month belongs in a column header only for reading, so don't build calculations on that layout.
Pivot and unpivot in SQL and Excel
SELECT region,
SUM(CASE WHEN month = '2026-07' THEN net END) AS jul,
SUM(CASE WHEN month = '2026-08' THEN net END) AS aug
FROM monthly
GROUP BY regionThat's a pivot written by hand. Some databases have PIVOT and UNPIVOT keywords, and elsewhere you can unpivot with one SELECT per column, joined by UNION ALL. In Excel, a PivotTable pivots for reading, while Power Query has Pivot Column and Unpivot Columns.