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.

Net value by region and month, one row per region per month
regionmonthnet
North2026-082,922
South2026-081,260
North2026-09900
South2026-093,780
North2026-071,756
South2026-07860
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

  1. Cross Tab is selected in this live Strata flow, turning month into 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.
  2. Select the Select block. Under Columns, it lists them the same way.
  3. Select the ▲ beside 2026-07 twice, so it sits just below region.
  4. Read the preview. The columns should run July, August, September, and each region now reads left to right.
  5. 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 region

That'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.