SQL Academy · Lesson

CASE Expressions and Pivot Queries

Use CASE WHEN inside aggregates to pivot tall tables into wide cross-tab reports.

CASE Expressions and Pivot Queries is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

CASE: Inline If/Else for SQL

CASE is the SQL ternary. Two forms:

-- Searched CASE
CASE WHEN x > 0 THEN 'positive'
     WHEN x < 0 THEN 'negative'
     ELSE 'zero' END

-- Simple CASE
CASE status
  WHEN 'A' THEN 'active'
  WHEN 'P' THEN 'pending'
  ELSE 'unknown'
END

CASE in SELECT

Compute a derived column:

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'whale'
         WHEN total >=  100 THEN 'regular'
         ELSE 'small'
       END AS bucket
FROM orders;

CASE in WHERE and ORDER BY

Custom filtering and ordering:

-- Custom sort:
ORDER BY
  CASE WHEN status = 'urgent' THEN 0 ELSE 1 END,
  created_at DESC;

-- Conditional filter:
WHERE CASE WHEN $1 = 'paid' THEN status = 'paid'
           ELSE status IN ('pending','paid')
      END;

CASE Inside Aggregates: Conditional Counts

The classic pivot trick:

SELECT user_id,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'paid'      THEN 1 ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY user_id;

FILTER: Cleaner Alternative

PostgreSQL's FILTER clause is more readable:

SELECT user_id,
       COUNT(*)                                     AS total,
       COUNT(*) FILTER (WHERE status = 'paid')      AS paid,
       COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY user_id;

Pivoting Months Across Columns

Convert long-format to wide-format with CASE + aggregate:

SELECT year,
       SUM(CASE WHEN month =  1 THEN revenue END) AS jan,
       SUM(CASE WHEN month =  2 THEN revenue END) AS feb,
       -- ...
       SUM(CASE WHEN month = 12 THEN revenue END) AS dec
FROM monthly_revenue
GROUP BY year
ORDER BY year;

Pivoting Categories

One row per user, one column per status:

SELECT user_id,
       SUM(total) FILTER (WHERE status = 'paid')      AS paid_total,
       SUM(total) FILTER (WHERE status = 'pending')   AS pending_total
FROM orders GROUP BY user_id;

Limitations of CASE Pivots

You must know the target columns at write time. For dynamic pivots, generate the SQL in the application or use procedural PL/pgSQL.

CASE vs COALESCE / NULLIF

COALESCE = "first non-NULL". NULLIF = "NULL if equal". CASE is more general — use it when COALESCE/NULLIF don't fit.

Type Compatibility

All CASE branches must yield compatible types. Cast explicitly if mixing types:

SELECT CASE WHEN x THEN 1::TEXT ELSE 'no' END;

Nested CASE

Multi-level branching:

SELECT CASE
  WHEN amount IS NULL THEN 'no payment'
  WHEN amount = 0     THEN 'free'
  WHEN amount < 10    THEN 'cheap'
  ELSE
    CASE WHEN paid_at IS NULL THEN 'overdue' ELSE 'paid' END
END AS status FROM invoices;

Recap

CASE is the universal "if/else" in SQL.

SELECT computed columns
Pivots via CASE-in-aggregate or FILTER
Custom WHERE / ORDER BY logic

Quick Check

Which clause is the most readable for conditional aggregation in PostgreSQL?

Frequently asked questions

Is the “CASE Expressions and Pivot Queries” lesson free?

Yes — the full text of “CASE Expressions and Pivot Queries” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “CASE Expressions and Pivot Queries”?

Use CASE WHEN inside aggregates to pivot tall tables into wide cross-tab reports. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “CASE Expressions and Pivot Queries” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

UNION, INTERSECT, EXCEPT
UNION ALL vs UNION (Deduplication Cost)
CASE Expressions and Pivot Queries
Crosstab Patterns (PostgreSQL crosstab())

← Back to SQL Academy