Skip to content
filebender

Pivot

Transform

Convert long-format data into wide format. Distinct values in the pivot column become new column headers; numeric values from another column are aggregated per row key. Use it to build summary cross-tabs, prepare data for spreadsheets, or invert an Unpivot earlier in the flow.

How it works

Section titled “How it works”

Pivot is a full-dataset batch transform. It buffers the input, groups rows by the row-key columns, and promotes each distinct value of the pivot column into a new output column whose cell is an aggregate over the matching value column.

The output schema is data-dependent: column count grows with the number of distinct pivot values seen in the input. Empty cells (where a row-key/pivot-value combination has no source rows) are filled with 0 for sum and count aggregations and with "" for min, max, and avg.

When a single value spec is configured the new columns are named after the pivot value directly. With two or more value specs the column name takes the form {pivotValue}_{aggregation}_{valueColumn} to avoid collisions. The pivot column itself is dropped from the output (its values become headers); the value column is also dropped (its cells become aggregated cells).

If pivotColumns or values is empty, rows pass through unchanged.

Input: One tabular data connection. Output: A wide table with row-key columns, then one (or more) generated column per distinct pivot value.

Options

Section titled “Options”
OptionTypeDefaultDescription
pivotColumnsstring[][]Columns whose distinct values become new output columns. With multiple, values are joined with _ to form a composite header.
rowKeysstring[][]Columns that identify each output row. Cells with the same row-key combination collapse into one row.
valuesArray<{ column, aggregation }>[]Each entry names a numeric column and an aggregation: sum, count, avg, min, or max. Multiple entries produce multiple cells per pivot value.

Examples

Section titled “Examples”

Pivot quarterly sales by customer

Section titled “Pivot quarterly sales by customer”

You have a long-format table of quarterly sales and want one row per customer with a column per quarter.

Before:

customerquartersales
Acme Corpq11200
Acme Corpq21500
Acme Corpq31800
Acme Corpq42100
Beta Incq1800
Beta Incq2900
Beta Incq31100
Beta Incq41300

Configuration: pivot columns: ["quarter"], row keys: ["customer"], values: [{ column: "sales", aggregation: "sum" }].

After:

customerq1q2q3q4
Acme Corp1200150018002100
Beta Inc80090011001300

Multiple aggregations produce composite column names

Section titled “Multiple aggregations produce composite column names”

You want both total and order count per quarter so you can spot quarters with many small orders.

Before:

customerquartersales
Acme Corpq1600
Acme Corpq1600
Acme Corpq21500
Beta Incq1800
Beta Incq2450
Beta Incq2450

Configuration: pivot columns: ["quarter"], row keys: ["customer"], values: [{ column: "sales", aggregation: "sum" }, { column: "sales", aggregation: "count" }].

After:

customerq1_sum_salesq1_count_salesq2_sum_salesq2_count_sales
Acme Corp1200215001
Beta Inc80019002

Composite pivot columns

Section titled “Composite pivot columns”

You want a cross-tab of region and channel as a single header.

Before:

productregionchannelrevenue
WidgetEUonline500
WidgetEUretail300
WidgetUSonline800
GadgetEUonline200
GadgetUSretail400

Configuration: pivot columns: ["region", "channel"], row keys: ["product"], values: [{ column: "revenue", aggregation: "sum" }].

After:

productEU_onlineEU_retailUS_onlineUS_retail
Widget5003008000
Gadget20000400

Tips and Edge Cases

Section titled “Tips and Edge Cases”
  • Default values for missing cells differ by aggregation. When a row-key/pivot-value pair has no source rows, the cell is filled with 0 for sum and count, and with "" (empty string) for min, max, and avg. Be careful when chaining a Change Type to number downstream — the "" cells coerce to null, not zero. See apps/web/src/transforms/pivot/logic.ts:72-75.
  • Pivot column order follows first-seen input order. The output columns appear in the order each distinct pivot value was first encountered while scanning the input rows. If you need alphabetical or numeric order (e.g. q1, q2, q3, q4 regardless of input shuffling), Sort Rows by the pivot column upstream. See apps/web/src/transforms/pivot/logic.ts:135-148.
  • A row key that also appears in pivotColumns or values is silently dropped from the row-key set. The intersection guard at apps/web/src/transforms/pivot/logic.ts:125-127 filters out collisions, so listing the same column in two slots is not an error — it is just ignored as a row key.
Section titled “Related Transforms”
  • Unpivot — the inverse: melt wide pivot output back into long format.
  • Group By — aggregate without reshaping; useful when you want totals but not new columns per pivot value.
  • Sort Rows — control pivot column order by sorting the source on the pivot column first.
© FileBender