Pivot / Unpivot

Pivot / Unpivot reshapes your dataset between long and wide formats. Pivot takes unique values from a categorical column and turns each one into its own column header, filling cells with values from a numerical column. Unpivot does the reverse - it stacks selected columns into key-value row pairs, increasing row count while reducing column count. Both subtypes are in one operation, toggled by a tab selector.

When to Use

Use Pivot when:

• Converting monthly sales rows into month columns for a side-by-side report.
• Building a crosstab of revenue by region and product tier.
• Preparing data for a spreadsheet that expects one column per category value.

Use Unpivot when:

• Converting a wide spreadsheet (one column per year) into a long format for BI tools.
• Stacking revenue, cost, and profit columns into a single metric column with a value column.
• Normalizing data before joining with a long-format reference table.

Sample Dataset

The examples in this doc use the Edilitics sample orders dataset. Download it to follow along in your own workspace.

Relevant columns:

How to Apply Pivot / Unpivot

Pivot (Long to Wide)

Pivot creates one column per unique value in the Column Field, fills cells with values from the Value Field, and aggregates when multiple rows share the same Index Column value and Column Field value.

Fields:

Aggregate functions:

Preview output columns button:

Once Index Column, Column Field, and Value Field are all selected, a Preview output columns button appears. Click it to see how many new columns will be created and the first 5 column names before saving.

Unpivot (Wide to Long)

Unpivot stacks selected columns into rows, producing a key column (variable name) and a value column. Row count equals original rows multiplied by the number of Value Columns selected.

Fields:

Live row count preview:

When Index Columns and Value Columns are both selected, a preview message appears: "Will produce N x rows per input row, with columns: [index columns], [variable name], [value name]."

Before and After

Pivot example

Index Column: region. Column Field: product_tier. Value Field: revenue. Aggregate: Sum.

Input (long format, 500 rows):

Output (wide format, 5 rows - one per region):

Row count reduced from 500 to 5. Three new columns from the 3 unique product_tier values.

Unpivot example

Index Columns: region. Value Columns: Enterprise, Growth, Starter. Variable column name: tier. Value column name: revenue.

Input (wide format, 5 rows):

Output (long format, 15 rows - 5 x 3 columns):

Row count multiplied by 3 (number of Value Columns). Two new columns: tier and revenue.

Code Equivalent

-- Pivot (PostgreSQL crosstab)
SELECT
  region,
  SUM(CASE WHEN product_tier = 'Enterprise' THEN revenue END) AS "Enterprise",
  SUM(CASE WHEN product_tier = 'Growth' THEN revenue END) AS "Growth",
  SUM(CASE WHEN product_tier = 'Starter' THEN revenue END) AS "Starter"
FROM orders
GROUP BY region;

-- Unpivot (PostgreSQL UNNEST)
SELECT region, tier, revenue
FROM orders
CROSS JOIN LATERAL (
  VALUES ('Enterprise', enterprise), ('Growth', growth), ('Starter', starter)
) AS t(tier, revenue);

import polars as pl

# Pivot: region rows, product_tier columns, sum of revenue
df_wide = df.pivot(
    values="revenue",
    index="region",
    columns="product_tier",
    aggregate_function="sum"
)

# Unpivot: stack Enterprise, Growth, Starter into rows
df_long = df_wide.melt(
    id_vars=["region"],
    value_vars=["Enterprise", "Growth", "Starter"],
    variable_name="tier",
    value_name="revenue"
)

Operation Reference

Pivot:

Unpivot:

Frequently Asked Questions

Next Steps

Need help? Email support@edilitics.com with your workspace, job ID, and context. We reply within one business day.