Shape / table · All operations

Unpivot a table

Columns to rows, melt.

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

Columns to rows

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

SQL
CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
UNPIVOT (SELECT customer, lat, lon FROM customers) ON lat, lon INTO NAME measure VALUE reading;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 8 AND count(DISTINCT measure) = 2 AND max(reading) FILTER (WHERE customer = 'Alice Ng' AND measure = 'lat') = 52.52 FROM result)) THEN 'as expected' ELSE error('four customers and two measures did not become eight rows') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “four customers and two measures did not become eight rows”.
  • 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 (3)
  • Arrangeread 2026-09-27

    Manually rearrange fields so each record becomes several records with named output columns

  • Transposeread 2026-09-27

    Pivot horizontal data fields onto a vertical axis as name and value rows

  • Transpose In-DBread 2026-09-27

    Unpivot columns to rows inside the database; removed in 2025.2

AWS Glue (1)
Azure Data Factory (1)
  • Unpivotread 2026-09-27

    Turn columns into row values

dbt-utils (1)
  • unpivotread 2026-09-27

    Turn columns of a relation into field_name/value rows, like pandas melt

DuckDB (1)
  • UNPIVOTread 2026-09-27

    Turn columns into rows of name and value

Duckle (1)
  • Unpivotread 2026-09-25

    columns to name and value rows with UNPIVOT

EasyMorph (1)
  • Unpivotread 2026-09-27

    Turn a matrix table's columns into rows

KNIME Analytics Platform (1)
  • Unpivotread 2026-09-27

    Rotate selected columns into rows, repeating the retained columns

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

    Turn columns into variable and value rows; deprecated in favour of unpivot

  • DataFrame.unpivotread 2026-09-27

    Turn columns into variable and value rows, keeping index columns

Power Query M (2)
  • Table.Unpivotread 2026-09-27

    Turn a named set of columns into attribute and value rows

  • Table.UnpivotOtherColumnsread 2026-09-27

    Turn every column except a named set into attribute and value rows

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

    Turn a set of columns into rows of one value column plus an index column

Tableau Prep (3)
  • Pivotread 2026-09-27

    Reshape columns to rows or rows to columns

  • Pivot columns to rowsread 2026-09-27

    Turn one or more groups of columns into name and value rows: wide to tall

  • Use wildcard search to pivotread 2026-09-27

    Pivot columns to rows for every field matching a name pattern; new matches join on rerun

Trifacta (2)
  • Unpivotread 2026-09-27

    Turn columns into rows, producing key and value columns

  • Unpivot Transformread 2026-09-27

    Merge several columns into key and value columns, one row per input column

VisiData (2)
  • meltread 2026-09-23

    Open Melted Sheet: keep key columns, turn other columns into Variable/Value rows

  • melt-regexread 2026-09-23

    Melt with a regex whose capture groups split column names into variable columns

Background

Unpivoting turns wide data, with each variable in its own column, into long data: one column holding the values and another naming each value's context.[1] In tidyr, pivot_longer() lengthens data, increasing the number of rows and decreasing the number of columns.[2]

When to use it

In tidyr, pivot_longer() is commonly needed to tidy wild-caught datasets, which often optimise for ease of data entry or ease of comparison rather than ease of analysis.[3] Reach for an unpivot when a run of columns such as months, years or survey answers are really values of one variable, and the analysis wants to filter, group or chart by that variable. (Meridian's view)

In SQL Server, NULL values in the input of UNPIVOT disappear in the output.[4] In tidyr, values_drop_na = TRUE drops rows that contain only NAs in the value column, turning explicit missing values into implicit ones.[2] In tidyr, values_drop_na should generally be used only when the missing values were created by the data's structure.[2] Before unpivoting, decide whether an empty cell is a missing measurement or a combination that never existed, and check the output's row count against the number of columns times the number of rows. (Meridian's view)

In Snowflake, the columns in an UNPIVOT column list must have exactly the same data type, with exceptions for text strings of different lengths and text types.[5] In tidyr, the value column pivot_longer() creates takes the common type of the input columns, and the names column is character unless names_transform converts it.[2] Unpivot only columns that measure the same kind of thing in the same unit, and cast the name column to a date or number where the headers encode one. (Meridian's view)

The method

Tidy data, set out by Hadley Wickham in the Journal of Statistical Software in 2014, gives datasets a specific structure: each variable is a column, each observation is a row, and each type of observational unit is a table.[6]

In Snowflake, UNPIVOT takes a list of columns and generates a row for each column specified in the list.[5] In Snowflake, the column names populate the name column and the column values populate the value column.[5] In SQL Server, because UNPIVOT projects column names as values, the data type of the UNPIVOT name column is nvarchar(128).[4]

DuckDB implements both the SQL Standard UNPIVOT syntax and a simplified UNPIVOT syntax.[7] In DuckDB, UNPIVOT supports more than two destination columns, which reduces the extent to which a dataset is pivoted without stacking every pivoted column.[7]

In tidyr, pivot_longer() can split each column name into several variables, given several names in names_to and a grouped regular expression in names_pattern.[8] In tidyr, pivot_longer() and pivot_wider() were introduced to improve the usability of gather() and spread().[3]

Compared with the alternatives

Converting a narrow table to a wide one is generally referred to as pivoting.[1] In SQL Server, UNPIVOT is not the exact reverse of PIVOT: PIVOT aggregates and merges rows, so UNPIVOT cannot reproduce the original result.[4] In Snowflake, UNPIVOT is not exactly the reverse of PIVOT because it cannot undo aggregations made by PIVOT.[5] In tidyr, pivot_wider() is the inverse transformation of pivot_longer().[2] Treat a pivot followed by an unpivot as a round trip only when every cell of the pivot came from a single row. (Meridian's view)

Sources

  1. [1]Wide and narrow datasecondary source
  2. [2]Pivot data from wide to long — pivot_longer • tidyrdocumentation
  3. [3]Pivoting • tidyrdocumentation
  4. [4]Using PIVOT and UNPIVOT - SQL Server | Microsoft Learndocumentation
  5. [5]UNPIVOT | Snowflake Documentationdocumentation
  6. [6]Tidy Data | Journal of Statistical Softwaresecondary source
  7. [7]UNPIVOT Statement – DuckDBdocumentation
  8. [8]Tidy data • tidyrdocumentation