0Pricing
SQL Academy · Lesson

CASE Expressions and Pivot Queries

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

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;

All lessons in this course

  1. UNION, INTERSECT, EXCEPT
  2. UNION ALL vs UNION (Deduplication Cost)
  3. CASE Expressions and Pivot Queries
  4. Crosstab Patterns (PostgreSQL crosstab())
← Back to SQL Academy