Unpivot

Stack a group of columns into rows. Wide tables become long tables.

What it does

Unpivot is the reverse of Pivot. You pick the wide columns. Each one becomes a row. The column name goes in one new column. The cell value goes in another.

Before. One column for each quarter:

After. Unpivot q1, q2 and q3 into quarter and sales:

Two rows become six rows. Two products times three quarters gives six.

Reshape wide to long

1. Type unpivot in the Search transforms box, in the Pipeline panel.
2. Select Unpivot in the results. The panel opens below the grid.
3. Open Pick the wide columns to stack into rows… and select your columns.
4. Open Columns to repeat for every output row… and select your ID columns.
5. Type a name in Name column. This column holds the old column names.
6. Type a name in Value column. This column holds the cell values.
7. Click Apply.

The grid updates at once. The step appears in your pipeline, where you can edit or delete it later.

The two output names

Name column starts as variable. Value column starts as value. Replace both with words from your data. In the example above they are quarter and sales.

The two names must be different. The panel shows an error if they match.

Tips

• Keep as ID columns (optional) is optional. Leave it empty and every other column repeats on each output row.
• A column that you stack does not appear in the ID column list.
• Pick columns that hold the same kind of data. Stack numbers with numbers.
• The row count grows. Multiply your rows by the number of stacked columns.
• Unpivot first, then use Group & Aggregate. Long data is easier to summarize.

For SQL users

The step runs as a DuckDB UNPIVOT statement:

UNPIVOT (SELECT "product", "q1", "q2", "q3" FROM data) ON "q1", "q2", "q3" INTO NAME "quarter" VALUE "sales"

Related Operations

• Pivot - Pivot table (rows to columns)
• Window Function - Rank, row number, lead, lag
• Smallest - Get nth smallest value as a column