---
title: "PIVOT clause"
description: "The PIVOT clause is a SQL feature that transforms rows into columns, making it easier to create summary tables and cross-tabulations of your data."
canonical: "https://motherduck.com/glossary/pivot-clause/"
related:
  - title: "PIVOT | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/pivot/"
  - title: "Why Use DuckDB for Analytics?"
    url: "https://motherduck.com/blog/six-reasons-duckdb-slaps/"
  - title: "Dive into Pivoting! | MotherDuck Dive Gallery"
    url: "https://motherduck.com/dive-gallery/dives/dive-into-pivoting/"
gated_asset:
  title: "Dives: Interactive Dashboards & Embedded Analytics"
  url: "https://motherduck.com/product/dives/"
---

# PIVOT clause

> The PIVOT clause is a SQL feature that transforms rows into columns, making it easier to create summary tables and cross-tabulations of your data.

## Overview
The `PIVOT` clause is a SQL feature that transforms rows into columns, making it easier to create summary tables and cross-tabulations of your data. DuckDB's implementation of `PIVOT` is more streamlined than traditional databases, eliminating the need to explicitly specify the values you want to pivot on.

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

## DuckDB Syntax
In DuckDB, the basic syntax is:
```sql
PIVOT dataset ON column_to_pivot USING aggregation_function
```

## Examples

First, let's create some sample sales data:

```sql
CREATE TABLE sales AS 
SELECT * FROM (
  VALUES 
    ('2023-01', 'Electronics', 1000),
    ('2023-01', 'Clothing', 800),
    ('2023-02', 'Electronics', 1200),
    ('2023-02', 'Clothing', 900),
    ('2023-03', 'Electronics', 950),
    ('2023-03', 'Clothing', 850)
) AS t(month, category, amount);
```

Basic pivot example to show sales by category across months:
```sql
PIVOT sales 
ON month 
USING sum(amount);
```

Output:
```
┌─────────────┬─────────┬─────────┬─────────┐
│  category   │ 2023-01 │ 2023-02 │ 2023-03 │
│   varchar   │ int128  │ int128  │ int128  │
├─────────────┼─────────┼─────────┼─────────┤
│ Electronics │    1000 │    1200 │     950 │
│ Clothing    │     800 │     900 │     850 │
└─────────────┴─────────┴─────────┴─────────┘
```

You can also use multiple aggregations in the same pivot:
```sql
PIVOT sales 
ON month 
USING sum(amount) AS total, avg(amount) AS average;
```

## Comparison to Other Databases
In traditional databases like PostgreSQL or SQL Server, you typically need to:
1. Explicitly list the values you want to pivot on
2. Write more verbose syntax with multiple subqueries
3. Sometimes use conditional aggregation with `CASE` statements

DuckDB simplifies this by automatically detecting the distinct values to pivot on and providing a more intuitive syntax. This makes it particularly useful for exploratory data analysis where you might not know all possible values in advance.

## Common Use Cases
The `PIVOT` clause is particularly useful for:
- Creating financial reports with months or quarters as columns
- Analyzing survey responses with responses as columns
- Building cross-tabulation reports
- Converting long/narrow data formats to wide formats for visualization
- Creating pivot tables similar to those in spreadsheet applications