---
title: "Pivot and unpivot wide and long data | Oplema"
description: "Learn the difference between wide and long data, when to use each shape and how to pivot and unpivot between them, with SQL and Excel equivalents."
url: "https://oplema.com/learn/data-preparation/transpose-and-crosstab"
updated: "2026-11-04"
---

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](https://oplema.com/strata), Oplema's browser app for data prep, [Cross Tab](https://oplema.com/docs/strata/transform/cross-tab) pivots and [Transpose](https://oplema.com/docs/strata/transform/transpose) unpivots.

This lab reshapes the net value of [fictional Vandelay Coffee Supply](https://oplema.com/learn/data-preparation/what-is-data-prep) by region and month, starting long.

Net value by region and month, one row per region per month
| 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

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.

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**.
