Pivoting Query Results in SQL: Using CASE WHEN and GROUP BY to Turn Rows into Columns

Introduction

You've run a query. The data is all there — every row you need — but it's stacked vertically in a way that makes the report completely useless. Your boss wants to see monthly sales for each product category across the columns, but what you've got is a thousand rows of (month, category, amount) tuples. The data is correct. The shape is wrong.

This is one of the most common problems in real-world SQL reporting, and it has a name: you need to pivot your results. Pivoting means rotating data from a "long" format (many rows, few columns) into a "wide" format (fewer rows, more columns). The result looks like a spreadsheet cross-tab — categories or time periods marching left to right across the top, with summary values filling the cells below.

The good news is you don't need a special PIVOT keyword to get there (though some databases offer one). By combining two things you likely already know — CASE WHEN expressions and GROUP BY aggregation — you can pivot any dataset in any SQL dialect. By the end of this lesson, you'll be building cross-tab reports from scratch, handling NULL edge cases, and understanding exactly when to use this technique versus reaching for a different tool entirely.

What you'll learn:

• How the long-to-wide transformation actually works conceptually
• How to use CASE WHEN inside aggregate functions to isolate values per column
• How to build a complete pivot query step by step, from raw data to polished report
• How to handle NULL values, sparse data, and dynamic column sets
• When to use the manual CASE WHEN pivot approach versus database-specific PIVOT syntax or application-layer solutions

---

Prerequisites

You should be comfortable with the following before diving in:

• Writing SELECT queries with filtering — see SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries if you need a refresher
• How GROUP BY and aggregate functions like SUM, COUNT, and AVG work — Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG covers this in depth
• What CASE WHEN expressions do and how they're structured — Writing SQL CASE Expressions: Conditional Logic Inside SELECT, WHERE, and GROUP BY is the primer you want

You don't need to know anything database-specific yet. The core technique works in PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

---

The Core Problem: Long Data vs. Wide Data

Before writing a single line of SQL, it helps to understand the shape you're starting with and the shape you're building toward.

Imagine you're a data analyst at a retail company. Your sales table stores every transaction summarized by month and product category:

-- The source table: sales
-- month       | category    | revenue
-- ------------|-------------|--------
-- 2024-01-01  | Electronics | 84200
-- 2024-01-01  | Clothing    | 31500
-- 2024-01-01  | Home & Garden | 22100
-- 2024-02-01  | Electronics | 91000
-- 2024-02-01  | Clothing    | 28900
-- 2024-02-01  | Home & Garden | 19800
-- 2024-03-01  | Electronics | 78600
-- 2024-03-01  | Clothing    | 35200
-- 2024-03-01  | Home & Garden | 24700

This is long format. There are three rows per month — one for each category. The structure is perfectly normalized and easy to insert into, but it's a nightmare to read as a report.

What your stakeholder actually wants to see is this:

-- month       | Electronics | Clothing | Home & Garden
-- ------------|-------------|----------|---------------
-- 2024-01-01  | 84200       | 31500    | 22100
-- 2024-02-01  | 91000       | 28900    | 19800
-- 2024-03-01  | 78600       | 35200    | 24700

That's wide format. Each category has become its own column. This is harder to store and update (every new category requires altering the schema), but it's infinitely easier to scan and compare.

---

The Mechanism: CASE WHEN Inside an Aggregate

The trick to understanding how this works is to think about what happens row by row, before the GROUP BY collapses everything.

Consider a single CASE WHEN expression:

CASE WHEN category = 'Electronics' THEN revenue ELSE NULL END

Applied to every row in the sales table, this expression produces a value only for rows where the category is Electronics — and NULL for everything else. For any given month, that gives you something like this in an intermediate, pre-aggregation state:

-- month       | category      | revenue | electronics_col | clothing_col | homegarden_col
-- 2024-01-01  | Electronics   | 84200   | 84200           | NULL         | NULL
-- 2024-01-01  | Clothing      | 31500   | NULL            | 31500        | NULL
-- 2024-01-01  | Home & Garden | 22100   | NULL            | NULL         | 22100

Three rows for January. Each row "contributes" its revenue to exactly one column and leaves the rest as NULL.

Now here's where GROUP BY completes the pivot. When you group by month and apply SUM() to each of those conditional columns, the database collapses those three rows into one — and SUM() ignores NULLs by default, so each category column ends up with exactly the value you want:

SUM(CASE WHEN category = 'Electronics' THEN revenue ELSE NULL END)
-- January result: SUM(84200, NULL, NULL) = 84200

SUM(CASE WHEN category = 'Clothing' THEN revenue ELSE NULL END)
-- January result: SUM(NULL, 31500, NULL) = 31500

That's the entire mechanism. Everything else is just applying it cleanly.

---

Building Your First Pivot Query

Let's create the sample dataset and build the query from scratch. Here's the table setup:

CREATE TABLE sales (
    sale_month  DATE,
    category    VARCHAR(50),
    revenue     NUMERIC(12, 2)
);

INSERT INTO sales VALUES
    ('2024-01-01', 'Electronics',   84200),
    ('2024-01-01', 'Clothing',      31500),
    ('2024-01-01', 'Home & Garden', 22100),
    ('2024-02-01', 'Electronics',   91000),
    ('2024-02-01', 'Clothing',      28900),
    ('2024-02-01', 'Home & Garden', 19800),
    ('2024-03-01', 'Electronics',   78600),
    ('2024-03-01', 'Clothing',      35200),
    ('2024-03-01', 'Home & Garden', 24700);

Now the pivot query:

SELECT
    sale_month,
    SUM(CASE WHEN category = 'Electronics'   THEN revenue ELSE 0 END) AS electronics,
    SUM(CASE WHEN category = 'Clothing'      THEN revenue ELSE 0 END) AS clothing,
    SUM(CASE WHEN category = 'Home & Garden' THEN revenue ELSE 0 END) AS home_and_garden
FROM
    sales
GROUP BY
    sale_month
ORDER BY
    sale_month;

Result:

sale_month  | electronics | clothing | home_and_garden
------------|-------------|----------|----------------
2024-01-01  | 84200.00    | 31500.00 | 22100.00
2024-02-01  | 91000.00    | 28900.00 | 19800.00
2024-03-01  | 78600.00    | 35200.00 | 24700.00

Notice the ELSE 0 instead of ELSE NULL. For revenue columns, returning 0 when there's no match is usually more useful than NULL — it makes totals and comparisons work correctly without extra NULL-handling logic. We'll talk more about when to use NULL versus 0 in the troubleshooting section.

---

A More Realistic Scenario: Order Counts by Status and Department

Single-table pivots are a good starting point, but in production you're almost always joining tables before pivoting. Let's look at a more realistic scenario.

You're supporting an operations team that wants to see, for each department, how many orders are in each status (Pending, Processing, Shipped, Delivered). The data lives across two tables:

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    dept_id     INT,
    status      VARCHAR(20),
    order_date  DATE
);

CREATE TABLE departments (
    dept_id     INT PRIMARY KEY,
    dept_name   VARCHAR(50)
);

The query joins these tables, then pivots on status:

SELECT
    d.dept_name,
    COUNT(CASE WHEN o.status = 'Pending'    THEN 1 END) AS pending,
    COUNT(CASE WHEN o.status = 'Processing' THEN 1 END) AS processing,
    COUNT(CASE WHEN o.status = 'Shipped'    THEN 1 END) AS shipped,
    COUNT(CASE WHEN o.status = 'Delivered'  THEN 1 END) AS delivered
FROM
    orders o
    JOIN departments d ON o.dept_id = d.dept_id
GROUP BY
    d.dept_name
ORDER BY
    d.dept_name;

A few things to notice here:

Using COUNT instead of SUM. When you want to count rows rather than sum a numeric column, use COUNT(CASE WHEN ... THEN 1 END). The 1 is arbitrary — COUNT just needs a non-NULL value. When the condition doesn't match, the CASE expression returns NULL (no ELSE clause), and COUNT ignores NULLs automatically. This is cleaner than SUM(CASE WHEN ... THEN 1 ELSE 0 END) and slightly more expressive.

No ELSE clause. When you omit ELSE, the expression returns NULL for non-matching rows. For COUNT, this is exactly what you want. For SUM, you'll usually want ELSE 0 to avoid confusing NULL totals.

This technique works equally well whether you're looking at multi-table reporting with JOIN and GROUP BY or a single source table.

---

Using CTEs to Make Pivot Queries Readable

Complex pivot queries can get unwieldy fast, especially when the source data requires filtering, joining, or pre-aggregation before you pivot it. Common Table Expressions (CTEs) are your best friend here.

Suppose you want to pivot sales, but only for the top 5 salespeople by total revenue, and you need to show their monthly performance across Q1. Here's how to structure it with a CTE:

WITH top_reps AS (
    -- First, find the top 5 salespeople for the period
    SELECT
        rep_id
    FROM
        sales_detail
    WHERE
        sale_date BETWEEN '2024-01-01' AND '2024-03-31'
    GROUP BY
        rep_id
    ORDER BY
        SUM(revenue) DESC
    LIMIT 5
),

monthly_sales AS (
    -- Then aggregate their monthly performance
    SELECT
        sd.rep_id,
        r.rep_name,
        DATE_TRUNC('month', sd.sale_date) AS sale_month,
        SUM(sd.revenue)                   AS monthly_revenue
    FROM
        sales_detail sd
        JOIN reps r ON sd.rep_id = r.rep_id
        JOIN top_reps t ON sd.rep_id = t.rep_id
    WHERE
        sd.sale_date BETWEEN '2024-01-01' AND '2024-03-31'
    GROUP BY
        sd.rep_id, r.rep_name, DATE_TRUNC('month', sd.sale_date)
)

-- Finally, pivot months into columns
SELECT
    rep_name,
    SUM(CASE WHEN sale_month = '2024-01-01' THEN monthly_revenue ELSE 0 END) AS jan_revenue,
    SUM(CASE WHEN sale_month = '2024-02-01' THEN monthly_revenue ELSE 0 END) AS feb_revenue,
    SUM(CASE WHEN sale_month = '2024-03-01' THEN monthly_revenue ELSE 0 END) AS mar_revenue,
    SUM(monthly_revenue) AS q1_total
FROM
    monthly_sales
GROUP BY
    rep_name
ORDER BY
    q1_total DESC;

This query has three distinct logical phases, each in its own CTE: identify the relevant reps, pre-aggregate their monthly numbers, then pivot. If you tried to write this as one monolithic query, it would be extremely difficult to read or debug.

For more on how to structure these multi-step queries, Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture goes deep on the patterns.

---

Handling Sparse Data and NULL Values

Real-world data is messy. Not every combination of row key and column value will exist in your source table. This is where NULL handling becomes critical.

Imagine your sales table doesn't have every category for every month — maybe Home & Garden had no sales in February. Your pivot query with ELSE 0 will correctly return 0 for that cell. But what if you want to distinguish "zero sales" from "this category didn't exist yet"? In that case, you'd use ELSE NULL and then apply COALESCE at the final output layer:

SELECT
    sale_month,
    COALESCE(SUM(CASE WHEN category = 'Electronics'   THEN revenue END), 0) AS electronics,
    COALESCE(SUM(CASE WHEN category = 'Clothing'      THEN revenue END), 0) AS clothing,
    COALESCE(SUM(CASE WHEN category = 'Home & Garden' THEN revenue END), 0) AS home_and_garden
FROM
    sales
GROUP BY
    sale_month
ORDER BY
    sale_month;

The difference here is subtle but important: SUM() of all NULLs returns NULL, not 0. COALESCE(NULL, 0) then converts that NULL to 0. The result looks the same — but the logic makes your intention explicit. You're saying "if there were genuinely no matching rows, show 0."

For a deeper understanding of how NULL propagates through aggregations and why it matters, NULL Handling in SQL: IS NULL, COALESCE, and NULLIF is essential reading.

---

Pivoting Multiple Metrics at Once

Sometimes you need more than one metric per column. For example: both revenue and order count for each category. You can pivot multiple metrics simultaneously by simply adding more conditional aggregates:

SELECT
    sale_month,

    -- Revenue per category
    SUM(CASE WHEN category = 'Electronics'   THEN revenue ELSE 0 END) AS electronics_revenue,
    SUM(CASE WHEN category = 'Clothing'      THEN revenue ELSE 0 END) AS clothing_revenue,
    SUM(CASE WHEN category = 'Home & Garden' THEN revenue ELSE 0 END) AS homegarden_revenue,

    -- Order count per category
    COUNT(CASE WHEN category = 'Electronics'   THEN 1 END) AS electronics_orders,
    COUNT(CASE WHEN category = 'Clothing'      THEN 1 END) AS clothing_orders,
    COUNT(CASE WHEN category = 'Home & Garden' THEN 1 END) AS homegarden_orders,

    -- Average order value per category
    ROUND(
        AVG(CASE WHEN category = 'Electronics'   THEN revenue END), 2
    ) AS electronics_avg_order,
    ROUND(
        AVG(CASE WHEN category = 'Clothing'      THEN revenue END), 2
    ) AS clothing_avg_order,
    ROUND(
        AVG(CASE WHEN category = 'Home & Garden' THEN revenue END), 2
    ) AS homegarden_avg_order

FROM
    sales
GROUP BY
    sale_month
ORDER BY
    sale_month;

This produces a wide report with nine data columns: revenue, count, and average for each of three categories. A tool like Excel or a BI platform can then render this directly as a formatted table.

Notice the deliberate column naming convention: {category}_{metric}. Consistent naming makes these wide queries maintainable — someone reading the code three months later can immediately understand what each column contains without tracing through the CASE logic.

---

Database-Specific PIVOT Syntax

Some databases offer a dedicated PIVOT syntax that can handle simple cases more concisely. SQL Server and Oracle both support it. Here's the SQL Server equivalent of our basic revenue pivot:

-- SQL Server / Oracle syntax
SELECT
    sale_month,
    [Electronics],
    [Clothing],
    [Home & Garden]
FROM
    sales
PIVOT (
    SUM(revenue)
    FOR category IN ([Electronics], [Clothing], [Home & Garden])
) AS pivot_table
ORDER BY
    sale_month;

The syntax is more compact, and for simple cases it's readable. But the manual CASE WHEN approach has significant advantages:

Factor	CASE WHEN + GROUP BY	Database PIVOT syntax
Portability	Works in all SQL dialects	SQL Server, Oracle only
Multiple metrics	Easy — just add more CASE blocks	Cumbersome
Custom column names	Full control	Limited
Conditional logic in values	Fully supported	Not supported
Readability for complex queries	Better with CTEs	Gets unwieldy

PostgreSQL, MySQL, and SQLite have no native PIVOT keyword at all — the CASE WHEN approach is your only option in pure SQL.

---

The Dynamic Pivot Problem

Here's the uncomfortable truth about SQL pivoting: the columns in your output must be known at query-write time. If the list of categories in your database changes — a new product line launches, a department gets renamed — your pivot query stops being accurate without manual updates.

This is called the dynamic pivot problem, and it's a genuine limitation of SQL's static type system.

There are a few ways to handle it:

Option 1: Accept the limitation. For reports with a stable, well-defined set of columns (months of the year, day-of-week, fixed status values), this isn't actually a problem. Document which categories the query covers and review it quarterly.

Option 2: Generate SQL dynamically. Most application layers (Python, Java, stored procedures) can query the distinct values first, then build and execute the pivot SQL dynamically. In PostgreSQL this might look like a PL/pgSQL function that generates a query string; in Python you'd build the SQL string using the results of a SELECT DISTINCT category query.

Option 3: Pivot in your BI tool. Tools like Tableau, Looker, Power BI, and even Excel all have native cross-tab/pivot functionality. If your stakeholders are using a BI tool, the right architecture is often: write clean long-format SQL, and let the BI tool handle the pivoting. This gives you dynamic columns without SQL gymnastics.

Option 4: Use crosstab() in PostgreSQL. PostgreSQL's tablefunc extension provides a crosstab() function that can generate pivot tables with dynamic columns — though the syntax is notoriously awkward and still requires knowing column types ahead of time.

For a full treatment of advanced pivoting techniques including dynamic approaches, Advanced Pivoting and Unpivoting Data Transformations in SQL is the natural next step from this lesson.

---

Real-World Project: Building a Monthly KPI Summary Report

Let's put everything together in a single realistic project. You're a data analyst at an e-commerce company. The operations director wants a monthly KPI summary for Q1 2024, showing — for each fulfillment center — the number of orders by status, the total revenue, and the average days to ship.

Here's the schema:

CREATE TABLE fulfillment_orders (
    order_id          INT PRIMARY KEY,
    center_id         INT,
    order_date        DATE,
    shipped_date      DATE,
    delivered_date    DATE,
    status            VARCHAR(20),   -- 'Pending', 'Shipped', 'Delivered', 'Cancelled'
    order_value       NUMERIC(10, 2)
);

CREATE TABLE fulfillment_centers (
    center_id    INT PRIMARY KEY,
    center_name  VARCHAR(50),
    region       VARCHAR(30)
);

Here's the complete pivot query:

WITH q1_orders AS (
    -- Filter to Q1 and join center names
    SELECT
        fc.center_name,
        fo.order_date,
        fo.status,
        fo.order_value,
        -- Calculate days to ship; NULL if not yet shipped
        CASE
            WHEN fo.shipped_date IS NOT NULL
            THEN fo.shipped_date - fo.order_date
        END AS days_to_ship
    FROM
        fulfillment_orders fo
        JOIN fulfillment_centers fc ON fo.center_id = fc.center_id
    WHERE
        fo.order_date BETWEEN '2024-01-01' AND '2024-03-31'
)

SELECT
    center_name,

    -- Order counts by status
    COUNT(CASE WHEN status = 'Pending'   THEN 1 END) AS pending_count,
    COUNT(CASE WHEN status = 'Shipped'   THEN 1 END) AS shipped_count,
    COUNT(CASE WHEN status = 'Delivered' THEN 1 END) AS delivered_count,
    COUNT(CASE WHEN status = 'Cancelled' THEN 1 END) AS cancelled_count,
    COUNT(*) AS total_orders,

    -- Revenue metrics
    SUM(CASE WHEN status != 'Cancelled' THEN order_value ELSE 0 END) AS active_revenue,
    ROUND(AVG(CASE WHEN status != 'Cancelled' THEN order_value END), 2) AS avg_order_value,

    -- Fulfillment speed
    ROUND(AVG(days_to_ship), 1) AS avg_days_to_ship,
    MAX(days_to_ship)            AS max_days_to_ship,

    -- Cancellation rate as a percentage
    ROUND(
        100.0 * COUNT(CASE WHEN status = 'Cancelled' THEN 1 END) / COUNT(*),
        1
    ) AS cancellation_rate_pct

FROM
    q1_orders
GROUP BY
    center_name
ORDER BY
    active_revenue DESC;

This query does several things worth studying:

• The CTE handles the join and computes derived fields (days_to_ship) before the pivot, keeping the main query clean
• COUNT(*) gives the total orders, while individual status counts use conditional COUNT
• Revenue excludes cancelled orders using status != 'Cancelled' inside the CASE — a business rule encoded directly in the query
• The cancellation rate uses 100.0 * (not 100 *) to force floating-point division in databases that default to integer division
• AVG(days_to_ship) naturally ignores NULLs, so unshipped orders don't skew the average

This is a complete, production-ready reporting query. With minor adjustments for your specific schema, you could drop this directly into a scheduled report or a BI tool data source.

---

Hands-On Exercise

Use the following dataset to practice. Create and populate this table:

CREATE TABLE support_tickets (
    ticket_id   INT PRIMARY KEY,
    team        VARCHAR(30),
    priority    VARCHAR(10),   -- 'Low', 'Medium', 'High', 'Critical'
    created_at  DATE,
    resolved_at DATE           -- NULL if not yet resolved
);

INSERT INTO support_tickets VALUES
(1,  'Platform',  'High',     '2024-01-05', '2024-01-08'),
(2,  'Platform',  'Medium',   '2024-01-12', '2024-01-15'),
(3,  'Platform',  'Critical', '2024-01-20', '2024-01-21'),
(4,  'Platform',  'Low',      '2024-02-03', NULL),
(5,  'Platform',  'High',     '2024-02-14', '2024-02-17'),
(6,  'Mobile',    'Medium',   '2024-01-08', '2024-01-11'),
(7,  'Mobile',    'Critical', '2024-01-17', '2024-01-18'),
(8,  'Mobile',    'Low',      '2024-02-02', NULL),
(9,  'Mobile',    'High',     '2024-02-22', '2024-02-25'),
(10, 'Mobile',    'Medium',   '2024-03-01', '2024-03-04'),
(11, 'Data',      'Low',      '2024-01-10', '2024-01-14'),
(12, 'Data',      'Medium',   '2024-01-25', '2024-01-28'),
(13, 'Data',      'High',     '2024-02-08', NULL),
(14, 'Data',      'Critical', '2024-02-19', '2024-02-20'),
(15, 'Data',      'Medium',   '2024-03-05', '2024-03-07');

Exercise 1 — Basic Pivot: Write a query that shows, for each team, the count of tickets at each priority level (Low, Medium, High, Critical) as separate columns. Include a total ticket count column.

Exercise 2 — Multi-Metric Pivot: Extend Exercise 1 to also show, for each team: the number of resolved tickets, the number of unresolved tickets, and the average days to resolve (for resolved tickets only).

Exercise 3 — Monthly Pivot: Pivot the data differently: show each team as a row, with total ticket counts broken out by month (Jan, Feb, Mar) as columns. Add a Q1 total column.

Challenge: Combine Exercises 2 and 3 into a single query that shows monthly ticket counts AND resolution rates per month, all in one wide result set.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting GROUP BY — Getting One Row Instead of Many

This is the most common beginner error with pivot queries. If you write the conditional aggregates but forget GROUP BY, you get a single row summing everything:

-- Wrong: no GROUP BY means one row for the entire table
SELECT
    SUM(CASE WHEN category = 'Electronics' THEN revenue ELSE 0 END) AS electronics
FROM sales;
-- Returns: 253800 (all three months combined)

-- Right: GROUP BY sale_month gives one row per month
SELECT
    sale_month,
    SUM(CASE WHEN category = 'Electronics' THEN revenue ELSE 0 END) AS electronics
FROM sales
GROUP BY sale_month;

Mistake 2: Using ELSE 0 with AVG

As mentioned earlier, ELSE 0 is dangerous with AVG. It pulls the average toward zero by treating "not in this category" as a zero-value data point:

-- Wrong: AVG treats the ELSE 0s as real data points
AVG(CASE WHEN category = 'Electronics' THEN revenue ELSE 0 END)

-- Right: omit ELSE so non-matching rows return NULL and are excluded from AVG
AVG(CASE WHEN category = 'Electronics' THEN revenue END)

Mistake 3: String Matching Case Sensitivity

If your category values are case-sensitive and inconsistently entered, your CASE conditions will silently miss rows. Always check:

-- Diagnosing unexpected zeros in a pivot column:
SELECT DISTINCT category FROM sales ORDER BY category;
-- Might reveal: 'electronics', 'Electronics', 'ELECTRONICS'

-- Fix: normalize before comparing
CASE WHEN UPPER(category) = 'ELECTRONICS' THEN revenue END

Mistake 4: Counting Rows When You Should Count Distinct Entities

-- This counts order rows, which might have duplicates after a join:
COUNT(CASE WHEN status = 'Shipped' THEN 1 END)

-- This counts distinct orders:
COUNT(DISTINCT CASE WHEN status = 'Shipped' THEN order_id END)

After joining tables, especially with one-to-many relationships, row counts can multiply unexpectedly. If your pivot numbers seem too high, check for join inflation by comparing COUNT(*) before and after the join. The Debugging SQL Queries guide walks through exactly this kind of diagnostic process.

Mistake 5: Hardcoding Values That Will Change

If your business adds a new product category or status value, your pivot query silently omits it — no error, just a missing column. Build in a review process: add a comment to the query listing the known values, and schedule a periodic check:

-- Known categories as of 2024-Q1: Electronics, Clothing, Home & Garden
-- Review if new categories are added: SELECT DISTINCT category FROM sales
SELECT
    sale_month,
    SUM(CASE WHEN category = 'Electronics'   THEN revenue ELSE 0 END) AS electronics,
    SUM(CASE WHEN category = 'Clothing'      THEN revenue ELSE 0 END) AS clothing,
    SUM(CASE WHEN category = 'Home & Garden' THEN revenue ELSE 0 END) AS home_and_garden
    -- New categories would be silently excluded above
FROM sales
GROUP BY sale_month;

---

Performance Considerations

Pivot queries using CASE WHEN + GROUP BY are generally efficient — they scan the source data once and compute all the conditional aggregates in a single pass. There's no additional cost to having ten CASE WHEN columns versus two; the table is still scanned once.

That said, a few things can affect performance at scale:

Pre-filtering matters. Apply your WHERE clause before pivoting — either directly in the main query or in a CTE. Don't aggregate the entire table and filter the results afterward.

Indexes help with filtering, not pivoting. An index on the date column helps if you're filtering to a specific period. But the aggregation itself is a full scan of the filtered rows; indexes can't speed that up meaningfully.

Materializing intermediate results. For very large datasets (hundreds of millions of rows), it can be faster to materialize a pre-aggregated intermediate table rather than pivoting from raw transactions. Consider a scheduled summary table that stores monthly/weekly aggregations, then pivot from that.

Wide vs. narrow trade-offs. A pivot query with 50+ columns can become slow simply because of the number of conditional expressions being evaluated. If you're generating very wide pivots, test performance and consider whether the BI layer can absorb some of that work.

---

Summary & Next Steps

You now have a complete, practical toolkit for pivoting data in SQL. Let's recap what we covered:

• The core mechanism: CASE WHEN expressions tag each row with a value for its "column," returning NULL for non-matching rows. GROUP BY then collapses those rows, and aggregate functions like SUM and COUNT ignore NULLs — producing exactly one value per pivot column per group.
• Building pivot queries progressively: Start with the GROUP BY key columns, add one CASE WHEN per desired output column, and pick the right aggregate (SUM for totals, COUNT for counts, AVG with no ELSE for averages).
• Using CTEs to separate data preparation from the final pivot, keeping complex queries readable and debuggable.
• NULL vs. zero: Use ELSE 0 for SUM columns, omit ELSE (returning NULL) for COUNT and AVG.
• The dynamic pivot limitation: SQL columns are static at write time. Plan for it with documentation, dynamic SQL generation, or BI-layer pivoting.

Where to go next:

If you want to tackle the harder version of this problem — including how to unpivot (turn wide data back into long), how to use PostgreSQL's crosstab() function, and how to generate pivot SQL dynamically — continue with Advanced Pivoting and Unpivoting Data Transformations in SQL.

For the related skill of writing conditional aggregates with HAVING clauses — filtering groups after aggregation — Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice is the natural companion to this lesson.

And if you're working with time-series data in your pivots and need more powerful date manipulation — extracting months, truncating to periods, calculating date differences — Master SQL String and Date Functions: Essential Data Transformation Skills will give you the tools to handle any date-based pivot scenario.