Shape · Summarize and reshape

Pivot

Turns the values of one column into new columns, such as one row per store with a column per month. The reverse of Unpivot.

Also known as: Cross tab, long to wide

How it works

Choose the columns to Keep as rows (id columns), the column to Spread into columns from and the column the Values from. Each distinct value of the spread column becomes a column, filled with Aggregate with: Sum (the default), Count, Average, Min, Max, Median, Std dev, Count distinct, Concatenate, which joins the values with the Separator (, by default), or First. Concatenate and First take the values in the order rows arrive. With no id columns the rows are grouped by every remaining column. A combination with no rows gives a null cell. Unpivot does the reverse.

Inputs and outputs

Settings

{
  idColumns: string[]
  nameColumn: string
  valueColumn: string
  aggFunc: "SUM"|"COUNT"|"AVG"|"MIN"|"MAX"|"MEDIAN"|"STDDEV"|"COUNT_DISTINCT"|"CONCAT"|"FIRST"
  separator?: string (CONCAT only, default ",")
}

Example

Monthly sales rows should become one line per store. Keep store as rows, spread from month, take values from sales and aggregate with Sum.

More in Summarize and reshape

• Aggregate — Collapses rows into one row per group and works out values for each: sum, count, average, median, mode, percentiles, longest text and more, cash-flow values such as NPV or IRR, and for a geometry the dissolved shape, extent or centroid. With no group, you get one grand-total row.
• Group Aggregate — Works out a group total and writes it onto every row instead of collapsing them, as the value, a ratio or a percent. For example, each sale as a share of its region's total.
• Running Aggregations — Adds running totals that build up row by row: sum, average, median, min, max, count or standard deviation, optionally restarting per group. For example, year-to-date sales.
• Unpivot — Turns chosen columns into rows of name and value pairs. For example, Jan, Feb and Mar columns become month and sales rows.
• Transpose — Swaps rows and columns, so every column becomes a row. Best for small, wide summary tables.
• Split to Rows — Turns a cell holding several delimited values into one row per value. For example, 'red;green;blue' becomes three rows.
• Split to Columns — Breaks one text column into several columns at a delimiter, such as 'London, UK' into city and country.
• Explode — Turns each item of a list column into its own row. For comma-separated text, use Split to Rows instead.