Shape / table · All operations

Pivot a table

Rows to columns, cross-tab.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
the same cells, rearranged
Level
several clauses of SQL, or a window

The DuckDB step

Rows to columns

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
PIVOT orders ON product USING sum(amount) GROUP BY region ORDER BY region;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 5 AND max(widget) FILTER (WHERE region = 'west') = 1020 AND max(gizmo) FILTER (WHERE region = 'north') = 220 AND sum(gadget) + sum(gizmo) + sum(widget) = 1900.74 FROM result)) THEN 'as expected' ELSE error('the cells are not the sums expected') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the cells are not the sums expected”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • Cross Tabread 2026-09-27

    Pivot vertical values onto a horizontal axis, summarising where specified

AWS Glue (1)
  • Pivot Rows to Columnsread 2026-09-27

    Aggregate a numeric column while unique values of chosen columns become new columns

Azure Data Factory (1)
  • Pivotread 2026-09-27

    Aggregate while turning the distinct values of a column into columns

Bruin (1)
dbt-utils (1)
  • pivotread 2026-09-27

    Turn listed row values of a column into aggregated columns via CASE expressions

DuckDB (1)
  • PIVOTread 2026-09-27

    Turn the distinct values of a column into columns, aggregating the cells

Duckle (1)
  • Pivotread 2026-09-25

    rows to columns with the PIVOT statement

EasyMorph (1)
  • Pivotread 2026-09-27

    Turn a column's values into columns, producing a matrix table

KNIME Analytics Platform (2)
  • DB Pivotread 2026-09-27

    In-database: pivot and group a DB table with column aggregations

  • Pivotread 2026-09-27

    Group rows and turn the values of pivot columns into aggregated columns, with totals

OpenRefine (1)
Polars (2)
  • DataFrame.pivotread 2026-09-27

    Spread the values of a column into new columns, aggregating per index row

  • DataFrame.unstackread 2026-09-27

    Reshape a long frame to wide in fixed-size steps, with no grouping or aggregation

Power Query M (1)
  • Table.Pivotread 2026-09-27

    Turn the values of an attribute column into column headings, filled from a value column

RapidMiner Studio (1)
  • Pivotread 2026-09-27

    Group rows and spread a column's values into columns of aggregated values

SQLMesh (1)
  • @PIVOTread 2026-09-27

    Pivot a column on listed values into one aggregated column per value

Tableau Prep (1)
  • Pivot rows to columnsread 2026-09-27

    Turn the values of one field into columns filled with an aggregate of another field

Talend (1)
  • tPivotToColumnsDelimitedread 2026-09-27

    Pivot a column into columns with an aggregation function and write the result to a delimited file

Trifacta (2)
  • Pivotread 2026-09-27

    Create a column for each unique value of a column, holding an aggregate

  • Pivot Transformread 2026-09-27

    Aggregate by group, or turn the values of a column into columns holding a summary

VisiData (1)
  • pivotread 2026-09-23

    Open Pivot Table: rows by key columns, one column per value of current column, aggregated

Background

Pivoting turns a narrow table, with one column of values and another giving each value's context, into a wide table with each variable in its own column.[1] A pivot table holds aggregations, such as sums, averages or counts, of groups of values from a larger table within one or more discrete categories.[2] In SQL Server, PIVOT turns the unique values of one column into multiple output columns and runs aggregations where they are required.[3]

When to use it

Data in flat tables of only columns and rows can make summarised information hard to get, and a pivot table can help summarise it quickly.[2] A narrow table takes a new field without any change to its structure, but can be harder for people to understand than a wide one.[1] Keep the narrow table as the stored form, and pivot it at the point where a person needs to read it across the page. (Meridian's view)

In DuckDB, a PIVOT whose ON clause has no IN list detects the columns to create from the distinct values in the data.[4][5] In DuckDB, an IN list in the ON clause creates separate columns only for the values it names.[4] Where a later step depends on the pivoted columns' names, list the values with IN rather than leaving the column set to whatever the data holds. (Meridian's view) In DuckDB, pivoting on several columns creates a column for every combination of their values, and a combination absent from the data is filled with NULL.[4] In tidyr, the values_fill argument of pivot_wider sets the value that fills a cell when it is missing.[6]

In pandas, DataFrame.pivot does not aggregate, and raises a ValueError when an index and columns combination has more than one value.[7] In pandas, DataFrame.pivot_table is a generalisation of pivot that can handle duplicate values for one index and column pair.[7] In tidyr, the values_fn argument of pivot_wider is typically used when the identifying columns and the names column do not uniquely identify an observation.[6] In DuckDB, the first aggregate function changes the orientation of the data without aggregating, and works well for pivoting out a text column.[4] Before pivoting with first, check that each cell has only one row behind it. (Meridian's view) In DuckDB, a PIVOT with no USING clause counts the rows behind each cell, because USING defaults to count(*).[4] In DuckDB, FILTER clauses on aggregate functions in a PIVOT's USING clause are not supported and are silently ignored.[4]

In PostgreSQL, the crosstab function of the tablefunc module needs the names and types of its output columns defined in the FROM clause of the calling SELECT.[8]

The method

In a spreadsheet pivot table, fields from the source table are dragged onto a layout with four areas: filters, columns, rows and values.[2] In DuckDB's simplified PIVOT syntax, the ON clause names the columns to split into separate columns, matching the columns parameter of a spreadsheet pivot table.[4] In DuckDB's simplified PIVOT syntax, the USING clause sets how the split values are aggregated and GROUP BY sets the rows, matching a spreadsheet pivot table's values and rows.[4] In DuckDB's SQL Standard PIVOT syntax, an IN clause must be given for each column to be pivoted, and the simplified syntax is recommended for dynamic pivoting.[4]

DuckDB, like most SQL engines, requires all column names and types to be known at the start of a query, so a PIVOT that detects its columns is translated into multiple queries.[5] DuckDB finds the distinct values that should become columns using ENUM types, and injects each ENUM into one of the PIVOT statement's IN clauses.[5]

Bill Jelen and Mike Alexander, in their book Pivot Table Data Crunching, refer to Pito Salas as the father of pivot tables.[2] Lotus Development released Improv in 1991 on the NeXT platform, and Improv's users could define sets of categories and change views by dragging category names.[2] Early in 1994, Microsoft Excel 5 brought a new functionality called a PivotTable to market.[2] In 2007 Oracle Corporation made PIVOT and UNPIVOT operators available in Oracle Database 11g.[2]

Compared with the alternatives

In SQL Server, UNPIVOT carries out the opposite operation to PIVOT, rotating columns of a table-valued expression into column values.[3] In tidyr, pivot_wider widens data, increasing the number of columns and decreasing the number of rows, and pivot_longer is the inverse transformation.[6] In SQL Server, the PIVOT syntax is easier and more readable than the complex series of SELECT...CASE statements that might otherwise be needed.[3]

A contingency table, also called a cross tabulation or crosstab, is a matrix-format table that displays the multivariate frequency distribution of the variables.[9] The term contingency table was first used by Karl Pearson in a memoir published in 1904.[9]

Sources

  1. [1]Wide and narrow data - Wikipediasecondary source
  2. [2]Pivot table - Wikipediasecondary source
  3. [3]Using PIVOT and UNPIVOT - SQL Server | Microsoft Learndocumentation
  4. [4]PIVOT Statement – DuckDBdocumentation
  5. [5]Pivot Internals – DuckDBdocumentation
  6. [6]Pivot data from long to wide — pivot_wider • tidyrdocumentation
  7. [7]pandas.DataFrame.pivot — pandas 3.1.1 documentationdocumentation
  8. [8]PostgreSQL: Documentation: 18: F.43. tablefunc — functions that return tables (crosstab and others)documentation
  9. [9]Contingency table - Wikipediasecondary source