pbPassingBI
/
knime/lessons/kn-pivoting
Transformation & aggregation intermediate 5 min

Pivoting and Unpivoting

Rows to columns and back.

What you'll be able to do
  • Pivot with the Pivoting node
  • Unpivot wide data
  • Understand the generated column names

Unpivoting — wide to long

wide product | Jan | Feb A 100 120 B 90 140 melt pivot long product | month | value A Jan 100 A Feb 120 B Jan 90 charts and groupby want long data
Most reshaping work is moving between these two forms.

Pivoting — long to wide

The Pivoting node has three tabs: Groups (rows), Pivots (the column whose values become headers), and Aggregation (what fills the cells).

It aggregates by necessity, since several rows can land in one cell.

It produces two outputs: the pivot table itself, and a totals table.

Generated column names

Column names come from your data

Pivoting builds headers from data values, combined with the aggregation name — East+Sum(Revenue).

So the output schema changes when the data changes. A new region appearing next month adds a column, and any downstream node referencing a fixed list of columns breaks.

Where the workflow must survive that, use type- or pattern-based selection downstream rather than naming columns explicitly.

Which direction

Unpivot before analysis. Pivot at the end for presentation. A workflow that unpivots, aggregates, then pivots into a report layout is a common and readable shape.

Key points
  • Unpivoting turns value columns into rows — usually the first step
  • Pivoting generates column names from data, so the schema changes with the data
  • Use pattern-based selection downstream of a pivot
Check yourself