START FREE
← Back to Glossary

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.

DuckDB Syntax

In DuckDB, the basic syntax is:

Copy code
PIVOT dataset ON column_to_pivot USING aggregation_function

Examples

First, let's create some sample sales data:

Copy code
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:

Copy code
PIVOT sales 
ON month 
USING sum(amount);

Output:

Copy code
┌─────────────┬─────────┬─────────┬─────────┐
│  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:

Copy code
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

Related terms

CASE expression →

The CASE expression evaluates a list of conditions and returns a corresponding value for the first one that's true, functioning as SQL's if/else logic inside a query.

UNPIVOT →

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

ORDER BY clause →

The ORDER BY clause is a fundamental SQL command that lets you control the sequence in which your query results are returned.

USING clause →

The USING clause specifies join columns by name when both tables share identically named columns, joining on equality and returning a single copy of each shared column.

WHERE clause →

The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.

GROUP BY clause →

GROUP BY organizes rows into groups for aggregate calculations like SUM, COUNT, and AVG. Learn single and multi-column grouping, HAVING filters, and GROUP BY ALL.

READ NEXT