Search Shortcut cmd + k | ctrl + k

- Installation
- Documentation

- Getting Started
- Connect

- Overview
- Concurrency

- Data Import

- Overview
- Data Sources
- CSV Files

- Overview
- Auto Detection
- Reading Faulty CSV Files
- Tips

- JSON Files

- Overview
- Creating JSON
- Loading JSON
- Writing JSON
- JSON Type
- JSON Functions
- Format Settings
- Installing and Loading
- SQL to / from JSON
- Caveats

- Multiple Files

- Overview
- Combining Schemas

- Parquet Files

- Overview
- Metadata
- Encryption
- Tips

- Partitioning

- Hive Partitioning
- Partitioned Writes

- Appender
- INSERT Statements

- Client APIs

- Overview
- C

- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference

- C++
- CLI

- Overview
- Arguments
- Dot Commands
- Output Formats
- Editing
- Autocomplete
- Syntax Highlighting

- Dart
- Go
- Java
- Julia
- Node.js (Neo)

- Overview

- Node.js

- Overview
- API Reference

- Python

- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues

- R
- Rust
- Swift
- Wasm

- Overview
- Instantiation
- Data Ingestion
- Query
- Extensions

- ADBC
- ODBC

- Overview
- Linux Setup
- Windows Setup
- macOS Setup
- Configuration

- SQL

- Introduction
- Statements

- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH and DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DESCRIBE
- DROP
- EXPORT and IMPORT DATABASE
- INSERT
- LOAD / INSTALL
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM

- Query Syntax

- SELECT
- FROM and JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT and OFFSET
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements

- Data Types

- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting

- Expressions

- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries

- Functions

- Overview
- Aggregate Functions
- Array Functions
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- List Functions
- Map Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Struct Functions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Union Functions
- Utility Functions
- Window Functions

- Constraints
- Indexes
- Meta Queries

- Information Schema
- Metadata Functions

- DuckDB's SQL Dialect

- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility

- Samples

- Configuration

- Overview
- Pragmas
- Secrets Manager

- Extensions

- Overview
- Core Extensions
- Community Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Delta
- Excel
- Full Text Search
- httpfs (HTTP and S3)

- Overview
- HTTP(S) Support
- Hugging Face
- S3 API Support
- Legacy Authentication Scheme for S3 API

- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial

- Overview
- Function Reference
- R-Tree Indexes
- GDAL Integration

- SQLite
- Substrait
- TPC-DS
- TPC-H
- VSS

- Guides

- Overview
- Data Viewers

- Tableau
- CLI Charting with YouPlot

- Database Integration

- Overview
- MySQL Import
- PostgreSQL Import
- SQLite Import

- File Formats

- Overview
- CSV Import
- CSV Export
- Directly Reading Files
- Excel Import
- Excel Export
- JSON Import
- JSON Export
- Parquet Import
- Parquet Export
- Querying Parquet Files

- Network and Cloud Storage

- Overview
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- DuckDB over HTTPS / S3

- Meta Queries

- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment

- ODBC

- ODBC Guide

- Performance

- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks

- Python

- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems

- SQL Editors

- DBeaver SQL IDE

- SQL Features

- AsOf Join
- Full-Text Search
- query and query_table Functions

- Snippets

- Create Synthetic Data
- Sharing Macros

- Glossary of Terms
- Browse Offline

- Operations Manual

- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint

- Files Created by DuckDB
- Gitignore for DuckDB
- Reclaiming Space

- Securing DuckDB

- Overview
- Securing Extensions

- Development

- DuckDB Repositories
- Testing

- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch

- Profiling
- Release Calendar
- Building

- Overview
- Build Instructions
- Build Configuration
- Building Extensions
- Platforms
- Troubleshooting

- Benchmark Suite

- Internals

- Overview
- Storage Versions and Format
- Execution Format
- Pivot

- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo

UNPIVOT Statement

The `UNPIVOT` statement allows multiple columns to be stacked into fewer columns. In the basic case, multiple columns are stacked into two columns: a `NAME` column (which contains the name of the source column) and a `VALUE` column (which contains the value from the source column).

DuckDB implements both the SQL Standard `UNPIVOT` syntax and a simplified `UNPIVOT` syntax. Both can utilize a `COLUMNS` expression to automatically detect the columns to unpivot. `PIVOT_LONGER` may also be used in place of the `UNPIVOT` keyword.

For details on how the `UNPIVOT` statement is implemented, see the Pivot Internals site.

> The `PIVOT` statement is the inverse of the `UNPIVOT` statement.

## Simplified `UNPIVOT` Syntax

The full syntax diagram is below, but the simplified `UNPIVOT` syntax can be summarized using spreadsheet pivot table naming conventions as:

    UNPIVOT ⟨dataset⟩
    ON ⟨column(s)⟩
    INTO
        NAME ⟨name-column-name⟩
        VALUE ⟨value-column-name(s)⟩
    ORDER BY ⟨column(s)-with-order-direction(s)⟩
    LIMIT ⟨number-of-rows⟩;

### Example Data

All examples use the dataset produced by the queries below:

    CREATE OR REPLACE TABLE monthly_sales
        (empid INTEGER, dept TEXT, Jan INTEGER, Feb INTEGER, Mar INTEGER, Apr INTEGER, May INTEGER, Jun INTEGER);
    INSERT INTO monthly_sales VALUES
        (1, 'electronics', 1, 2, 3, 4, 5, 6),
        (2, 'clothes', 10, 20, 30, 40, 50, 60),
        (3, 'cars', 100, 200, 300, 400, 500, 600);

    FROM monthly_sales;

empid | dept | Jan | Feb | Mar | Apr | May | Jun
--- | --- | --- | --- | --- | --- | --- | ---
1 | electronics | 1 | 2 | 3 | 4 | 5 | 6
2 | clothes | 10 | 20 | 30 | 40 | 50 | 60
3 | cars | 100 | 200 | 300 | 400 | 500 | 600

### `UNPIVOT` Manually

The most typical `UNPIVOT` transformation is to take already pivoted data and re-stack it into a column each for the name and value. In this case, all months will be stacked into a `month` column and a `sales` column.

    UNPIVOT monthly_sales
    ON jan, feb, mar, apr, may, jun
    INTO
        NAME month
        VALUE sales;

empid | dept | month | sales
--- | --- | --- | ---
1 | electronics | Jan | 1
1 | electronics | Feb | 2
1 | electronics | Mar | 3
1 | electronics | Apr | 4
1 | electronics | May | 5
1 | electronics | Jun | 6
2 | clothes | Jan | 10
2 | clothes | Feb | 20
2 | clothes | Mar | 30
2 | clothes | Apr | 40
2 | clothes | May | 50
2 | clothes | Jun | 60
3 | cars | Jan | 100
3 | cars | Feb | 200
3 | cars | Mar | 300
3 | cars | Apr | 400
3 | cars | May | 500
3 | cars | Jun | 600

### `UNPIVOT` Dynamically Using Columns Expression

In many cases, the number of columns to unpivot is not easy to predetermine ahead of time. In the case of this dataset, the query above would have to change each time a new month is added. The `COLUMNS` expression can be used to select all columns that are not `empid` or `dept`. This enables dynamic unpivoting that will work regardless of how many months are added. The query below returns identical results to the one above.

    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales;

empid | dept | month | sales
--- | --- | --- | ---
1 | electronics | Jan | 1
1 | electronics | Feb | 2
1 | electronics | Mar | 3
1 | electronics | Apr | 4
1 | electronics | May | 5
1 | electronics | Jun | 6
2 | clothes | Jan | 10
2 | clothes | Feb | 20
2 | clothes | Mar | 30
2 | clothes | Apr | 40
2 | clothes | May | 50
2 | clothes | Jun | 60
3 | cars | Jan | 100
3 | cars | Feb | 200
3 | cars | Mar | 300
3 | cars | Apr | 400
3 | cars | May | 500
3 | cars | Jun | 600

### `UNPIVOT` into Multiple Value Columns

The `UNPIVOT` statement has additional flexibility: more than 2 destination columns are supported. This can be useful when the goal is to reduce the extent to which a dataset is pivoted, but not completely stack all pivoted columns. To demonstrate this, the query below will generate a dataset with a separate column for the number of each month within the quarter (month 1, 2, or 3), and a separate row for each quarter. Since there are fewer quarters than months, this does make the dataset longer, but not as long as the above.

To accomplish this, multiple sets of columns are included in the `ON` clause. The `q1` and `q2` aliases are optional. The number of columns in each set of columns in the `ON` clause must match the number of columns in the `VALUE` clause.

    UNPIVOT monthly_sales
        ON (jan, feb, mar) AS q1, (apr, may, jun) AS q2
        INTO
            NAME quarter
            VALUE month_1_sales, month_2_sales, month_3_sales;

empid | dept | quarter | month_1_sales | month_2_sales | month_3_sales
--- | --- | --- | --- | --- | ---
1 | electronics | q1 | 1 | 2 | 3
1 | electronics | q2 | 4 | 5 | 6
2 | clothes | q1 | 10 | 20 | 30
2 | clothes | q2 | 40 | 50 | 60
3 | cars | q1 | 100 | 200 | 300
3 | cars | q2 | 400 | 500 | 600

### Using `UNPIVOT` within a `SELECT` Statement

The `UNPIVOT` statement may be included within a `SELECT` statement as a CTE (a Common Table Expression, or WITH clause), or a subquery. This allows for an `UNPIVOT` to be used alongside other SQL logic, as well as for multiple `UNPIVOT`s to be used in one query.

No `SELECT` is needed within the CTE, the `UNPIVOT` keyword can be thought of as taking its place.

    WITH unpivot_alias AS (
        UNPIVOT monthly_sales
        ON COLUMNS(* EXCLUDE (empid, dept))
        INTO
            NAME month
            VALUE sales
    )
    SELECT * FROM unpivot_alias;

An `UNPIVOT` may be used in a subquery and must be wrapped in parentheses. Note that this behavior is different than the SQL Standard Unpivot, as illustrated in subsequent examples.

    SELECT *
    FROM (
        UNPIVOT monthly_sales
        ON COLUMNS(* EXCLUDE (empid, dept))
        INTO
            NAME month
            VALUE sales
    ) unpivot_alias;

### Expressions within `UNPIVOT` Statements

DuckDB allows expressions within the `UNPIVOT` statements, provided that they only involve a single column. These can be used to perform computations as well as explicit casts. For example:

    UNPIVOT
        (SELECT 42 AS col1, 'woot' AS col2)
        ON
            (col1 * 2)::VARCHAR,
            col2;

name | value
--- | ---
col1 | 84
col2 | woot

### Simplified `UNPIVOT` Full Syntax Diagram

Below is the full syntax diagram of the `UNPIVOT` statement.

## SQL Standard `UNPIVOT` Syntax

The full syntax diagram is below, but the SQL Standard `UNPIVOT` syntax can be summarized as:

    FROM [dataset]
    UNPIVOT [INCLUDE NULLS] (
        [value-column-name(s)]
        FOR [name-column-name] IN [column(s)]
    );

Note that only one column can be included in the `name-column-name` expression.

### SQL Standard `UNPIVOT` Manually

To complete the basic `UNPIVOT` operation using the SQL standard syntax, only a few additions are needed.

    FROM monthly_sales UNPIVOT (
        sales
        FOR month IN (jan, feb, mar, apr, may, jun)
    );

empid | dept | month | sales
--- | --- | --- | ---
1 | electronics | Jan | 1
1 | electronics | Feb | 2
1 | electronics | Mar | 3
1 | electronics | Apr | 4
1 | electronics | May | 5
1 | electronics | Jun | 6
2 | clothes | Jan | 10
2 | clothes | Feb | 20
2 | clothes | Mar | 30
2 | clothes | Apr | 40
2 | clothes | May | 50
2 | clothes | Jun | 60
3 | cars | Jan | 100
3 | cars | Feb | 200
3 | cars | Mar | 300
3 | cars | Apr | 400
3 | cars | May | 500
3 | cars | Jun | 600

### SQL Standard `UNPIVOT` Dynamically Using the `COLUMNS` Expression

The `COLUMNS` expression can be used to determine the `IN` list of columns dynamically. This will continue to work even if additional `month` columns are added to the dataset. It produces the same result as the query above.

    FROM monthly_sales UNPIVOT (
        sales
        FOR month IN (columns(* EXCLUDE (empid, dept)))
    );

### SQL Standard `UNPIVOT` into Multiple Value Columns

The `UNPIVOT` statement has additional flexibility: more than 2 destination columns are supported. This can be useful when the goal is to reduce the extent to which a dataset is pivoted, but not completely stack all pivoted columns. To demonstrate this, the query below will generate a dataset with a separate column for the number of each month within the quarter (month 1, 2, or 3), and a separate row for each quarter. Since there are fewer quarters than months, this does make the dataset longer, but not as long as the above.

To accomplish this, multiple columns are included in the `value-column-name` portion of the `UNPIVOT` statement. Multiple sets of columns are included in the `IN` clause. The `q1` and `q2` aliases are optional. The number of columns in each set of columns in the `IN` clause must match the number of columns in the `value-column-name` portion.

    FROM monthly_sales
    UNPIVOT (
        (month_1_sales, month_2_sales, month_3_sales)
        FOR quarter IN (
            (jan, feb, mar) AS q1,
            (apr, may, jun) AS q2
        )
    );

empid | dept | quarter | month_1_sales | month_2_sales | month_3_sales
--- | --- | --- | --- | --- | ---
1 | electronics | q1 | 1 | 2 | 3
1 | electronics | q2 | 4 | 5 | 6
2 | clothes | q1 | 10 | 20 | 30
2 | clothes | q2 | 40 | 50 | 60
3 | cars | q1 | 100 | 200 | 300
3 | cars | q2 | 400 | 500 | 600

### SQL Standard `UNPIVOT` Full Syntax Diagram

Below is the full syntax diagram of the SQL Standard version of the `UNPIVOT` statement.

##### About this page

- Report content issue
- Edit this page on GitHub