The Pain: Long-to-Wide Table Transformation Is Every Analyst’s Nightmare

Imagine you have a sales dataset in long format, where each row represents a product’s sales in a given month:

product | month | sales
-----------|---------|-------
Laptop | 2026-01 | 12000
Laptop | 2026-02 | 15000
Tablet | 2026-01 | 8000
Tablet | 2026-02 | 9500
Phone | 2026-01 | 22000
Phone | 2026-02 | 25000

Now your manager wants a monthly comparison report, with each month as a separate column:

product | 2026-01 | 2026-02
-----------|---------|--------
Laptop | 12000 | 15000
Tablet | 8000 | 9500
Phone | 22000 | 25000

In traditional SQL, this requires the CASE WHEN + GROUP BY combo:

SELECT
 product,
 SUM(CASE WHEN month = '2026-01' THEN sales ELSE 0 END) AS "2026-01",
 SUM(CASE WHEN month = '2026-02' THEN sales ELSE 0 END) AS "2026-02"
FROM sales_data
GROUP BY product;

When the number of months grows, this query becomes unwieldy. With 12 months, you need 12 CASE WHEN expressions. With dynamic columns, you might even need to construct SQL strings programmatically.

The good news: DuckDB provides native PIVOT syntax that handles all of this in a single, readable statement.

DuckDB PIVOT Syntax Deep Dive

Basic Usage: One Statement for Data Pivot

SELECT * FROM sales_data
PIVOT
 sales_data ON month
 USING SUM(sales)
 GROUP BY product;

The meaning is intuitive:

• PIVOT sales_data ON month: Use values from the month column as new column names
• USING SUM(sales): Apply SUM aggregation on the sales column
• GROUP BY product: Group results by product

The output is exactly the wide table you need:

product | 2026-01 | 2026-02
-----------|---------|--------
Laptop | 12000 | 15000
Tablet | 8000 | 9500
Phone | 22000 | 25000

Multiple Aggregations: Compute Several Metrics at Once

PIVOT supports multiple aggregate functions in a single operation:

SELECT * FROM sales_data
PIVOT
 sales_data ON month
 USING SUM(sales) AS total_sales, AVG(sales) AS avg_sales, COUNT(*) AS order_count
 GROUP BY product;

This generates 6 columns (3 months × 2 aggregations):

product | 2026-01_total_sales | 2026-02_total_sales | 2026-01_avg_sales | ...
-----------|--------------------|--------------------|--------------------|----
Laptop | 12000 | 15000 | 12000 | ...

Custom Column Names: Make Your Output Elegant

SELECT * FROM sales_data
PIVOT
 sales_data ON month
 USING SUM(sales)
 GROUP BY product
 COLUMNS ('January', 'February');

The COLUMNS clause lets you define custom output column names instead of using raw values directly.

Handling Missing Values: NULL vs Default

When some products have no sales records in certain months, PIVOT fills them with NULL. You can combine with COALESCE to set defaults:

SELECT
 product,
 COALESCE("2026-01", 0) AS jan_sales,
 COALESCE("2026-02", 0) AS feb_sales
FROM sales_data
PIVOT (SUM(sales) ON month GROUP BY product);

Complete Practical Examples

Scenario 1: E-commerce Monthly Sales Matrix

Suppose you run an e-commerce platform and need to generate a cross-category, cross-month revenue matrix for management decision-making.

-- Create sample data
CREATE TABLE monthly_sales AS
SELECT * FROM (VALUES
 ('Electronics', '2026-01', 50000),
 ('Electronics', '2026-02', 62000),
 ('Electronics', '2026-03', 58000),
 ('Clothing', '2026-01', 30000),
 ('Clothing', '2026-02', 35000),
 ('Clothing', '2026-03', 42000),
 ('Food', '2026-01', 20000),
 ('Food', '2026-02', 22000),
 ('Food', '2026-03', 25000)
) AS t(category, month, revenue);

-- Pivot the data
SELECT * FROM monthly_sales
PIVOT
 monthly_sales ON month
 USING SUM(revenue) AS total_revenue
 GROUP BY category;

Output:

category | 2026-01_total_revenue | 2026-02_total_revenue | 2026-03_total_revenue
----------|----------------------|----------------------|----------------------
Electronics| 50000 | 62000 | 58000
Clothing | 30000 | 35000 | 42000
Food | 20000 | 22000 | 25000

Scenario 2: Multi-dimensional Cross Analysis

PIVOT can be combined with UNPIVOT for bidirectional data transformation:

-- Pivot then unpivot for data cleaning
WITH pivoted AS (
 SELECT * FROM monthly_sales
 PIVOT (SUM(revenue) ON month GROUP BY category)
),
unpivoted AS (
 SELECT
 category,
 month,
 revenue
 FROM pivoted
 UNPIVOT (revenue ON month FROM total_revenue)
)
SELECT * FROM unpivoted
ORDER BY category, month;

Scenario 3: Production-ready Dynamic Column Handling

In production, months are dynamically changing. DuckDB supports querying metadata to dynamically construct column definitions:

-- Get all unique months as column definitions
SELECT DISTINCT month FROM monthly_sales ORDER BY month;

-- Results used to dynamically generate SQL or handle in application layer
-- 2026-01, 2026-02, 2026-03, ...

PIVOT vs Traditional Methods Comparison

Performance Advantages

For large datasets, PIVOT outperforms hand-written CASE WHEN approaches. The reasons:

1. Vectorized Execution: DuckDB’s PIVOT leverages columnar storage for vectorized aggregation in memory
2. Single Scan: Regardless of how many aggregate functions, PIVOT scans source data only once
3. Memory Optimization: Intermediate results are stored directly in wide-table format, avoiding subquery overhead

Benchmarks show PIVOT is 2-3x faster than equivalent CASE WHEN solutions on million-row datasets.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。