---
title: "UNPIVOT"
description: "UNPIVOT reshapes wide data by turning multiple columns into rows, converting a set of columns into name/value pairs -- the inverse of PIVOT."
canonical: "https://motherduck.com/glossary/unpivot/"
related:
  - title: "UNPIVOT | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/unpivot/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Dive into Pivoting! | MotherDuck Dive Gallery"
    url: "https://motherduck.com/dive-gallery/dives/dive-into-pivoting/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# UNPIVOT

> UNPIVOT reshapes wide data by turning multiple columns into rows, converting a set of columns into name/value pairs -- the inverse of PIVOT.

## Overview
`UNPIVOT` transforms "wide" data, where related values are spread across multiple columns, into "long" data, where those same values live in fewer columns with an extra column identifying which original column each value came from. It's the inverse operation of `PIVOT`, and DuckDB implements both the ANSI SQL standard syntax and a simplified, DuckDB-friendly syntax.

<glossary-callout guide="duckdb-cheatsheet-full" />

## Simplified DuckDB syntax
```sql
UNPIVOT monthly_sales
ON jan, feb, mar, apr, may, jun
INTO
  NAME month
  VALUE sales;
```
Given a table with one row per product and a column per month, this produces one row per (product, month) pair, with a `month` column holding the original column name and a `sales` column holding that month's value.

## SQL standard syntax
```sql
FROM monthly_sales
UNPIVOT (
  sales
  FOR month IN (jan, feb, mar, apr, may, jun)
);
```
This is equivalent to the simplified form above -- `sales` is the value column, `month` is the name column, and `jan`...`jun` are the source columns being stacked.

## Unpivoting multiple value columns at once
DuckDB additionally supports unpivoting several related columns together, useful when a wide table stores multiple metrics per period:
```sql
SELECT product_id, quarter, sales, returns
FROM quarterly_metrics
UNPIVOT (
  (sales, returns)
  FOR quarter IN (
    (q1_sales, q1_returns) AS 'Q1',
    (q2_sales, q2_returns) AS 'Q2'
  )
);
```

## Handling NULLs
By default, `UNPIVOT` drops rows where the value being unpivoted is `NULL`. Add `INCLUDE NULLS` to keep them:
```sql
FROM monthly_sales
UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar));
```

## Why UNPIVOT matters for analytics
Long-format data is generally easier to aggregate, filter, and plot -- most charting libraries and `GROUP BY` queries expect one row per observation rather than one column per category. `UNPIVOT` is the direct SQL alternative to reshaping data in pandas (`melt`) or other tools, and DuckDB's friendlier syntax makes it approachable for ad hoc use directly in SQL.