TECH **SQL for Data Analysis: Pivot Tables with CASE or PIVOT – Zero-Fluff Study Guide**

SQL for Data Analysis: Pivot Tables with CASE or PIVOT – Zero-Fluff Study Guide

1. What This Is & Why It Matters

Pivot tables transform rows into columns, turning long, normalized data into a wide, aggregated format that’s easier to analyze. In SQL, you do this with CASE expressions (universal) or PIVOT (SQL Server, Oracle, PostgreSQL, BigQuery).

Why it matters in production:
- Dashboards break without pivots. Most BI tools (Power BI, Tableau, Looker) expect data in a pivoted format. If you feed them raw transactional data, they’ll either fail or force you to pivot in the tool—slowing down reports.
- Legacy systems demand it. You’ll inherit 20-year-old SQL queries that use CASE for pivots. If you don’t understand them, you’ll waste hours debugging.
- Performance gains. Pivoting in SQL (not in Python/pandas) reduces data transfer and speeds up queries. A 10M-row table pivoted in SQL runs in seconds; pivoted in Python, it might crash your notebook.

Real-world scenario:
You’re analyzing e-commerce sales. Your raw data has one row per order item:

order_id | product | category | revenue
1        | Laptop  | Tech     | 1200
1        | Mouse   | Access   | 50
2        | Monitor | Tech     | 300

Your boss wants a monthly revenue report by category (Tech, Access, etc.). Without pivots, you’d write 10 separate queries. With pivots, you write one query and get:

month | Tech | Access | Total
Jan   | 1500 | 50     | 1550

2. Core Concepts & Components

• CASE expression
• Definition: Conditional logic in SQL (CASE WHEN ... THEN ... ELSE ... END).

• Production insight: CASE is portable—works in every SQL dialect. Use it when you need fine-grained control (e.g., handling NULLs, custom aggregations).

• PIVOT operator (SQL Server, Oracle, PostgreSQL, BigQuery)

• Definition: A dedicated syntax to rotate rows into columns.

• Production insight: PIVOT is cleaner but less flexible than CASE. Some databases (MySQL) don’t support it—stick to CASE for cross-platform queries.

• Aggregation function (SUM, COUNT, AVG)

• Definition: Required to collapse multiple rows into one value per pivot column.

• Production insight: Forgetting aggregation is the #1 cause of "duplicate rows" errors in pivots.

• Column aliasing (AS)

• Definition: Renaming pivot columns for readability.

• Production insight: Without aliases, your output columns will be named SUM(CASE WHEN ...)—unreadable in dashboards.

• GROUP BY

• Definition: Defines the rows in your pivoted output (e.g., GROUP BY month).

• Production insight: Missing GROUP BY collapses all data into one row.

• Dynamic vs. static pivots

• Definition:
  • Static: You hardcode pivot columns (e.g., Tech, Access).
  • Dynamic: Pivot columns are generated from data (requires dynamic SQL).
• Production insight: Static pivots are safer (no SQL injection risk). Dynamic pivots are risky but necessary for unknown categories (e.g., user-generated tags).

3. Step-by-Step Hands-On: Pivoting Sales Data

Prerequisites

• A SQL database (PostgreSQL, SQL Server, BigQuery, etc.).
• A table sales with columns: order_id, order_date, product, category, revenue.

Task: Monthly Revenue by Category (Static Pivot with CASE)

Goal: Transform this:

order_id | order_date | category | revenue
1        | 2023-01-15 | Tech     | 1200
2        | 2023-01-20 | Access   | 50
3        | 2023-02-05 | Tech     | 300

Into this:

month | Tech | Access | Total
Jan   | 1200 | 50     | 1250
Feb   | 300  | NULL   | 300

Step 1: Extract Month from order_date

SELECT
    TO_CHAR(order_date, 'Mon') AS month,  -- PostgreSQL
    -- EXTRACT(MONTH FROM order_date) AS month,  -- BigQuery
    -- FORMAT(order_date, 'MMM') AS month,  -- SQL Server
    category,
    revenue
FROM sales;

Step 2: Pivot with CASE

SELECT
    TO_CHAR(order_date, 'Mon') AS month,
    SUM(CASE WHEN category = 'Tech' THEN revenue ELSE 0 END) AS Tech,
    SUM(CASE WHEN category = 'Access' THEN revenue ELSE 0 END) AS Access,
    SUM(revenue) AS Total
FROM sales
GROUP BY TO_CHAR(order_date, 'Mon')
ORDER BY MIN(order_date);  -- Ensures Jan comes before Feb

Step 3: Verify Output

• Check for NULL values (expected if no sales in a category).
• Ensure Total = Tech + Access.

Alternative: Using PIVOT (SQL Server)

SELECT
    month,
    ISNULL(Tech, 0) AS Tech,  -- Replace NULL with 0
    ISNULL(Access, 0) AS Access,
    Tech + Access AS Total
FROM (
    SELECT
        FORMAT(order_date, 'MMM') AS month,
        category,
        revenue
    FROM sales
) AS SourceTable
PIVOT (
    SUM(revenue)
    FOR category IN ([Tech], [Access])
) AS PivotTable
ORDER BY MIN(order_date);

4. ? Production-Ready Best Practices

Performance

• Index pivot columns: If you pivot on category, add an index: sql CREATE INDEX idx_sales_category ON sales(category);
• Filter early: Reduce data before pivoting: sql WHERE order_date >= '2023-01-01' -- Filter before pivoting

Maintainability

• Use CTEs for clarity:
  sql WITH monthly_sales AS (
SELECT
TO_CHAR(order_date, 'Mon') AS month,
category,
revenue
FROM sales ) SELECT * FROM monthly_sales PIVOT (SUM(revenue) FOR category IN ([Tech], [Access]));
• Document pivot logic: Add comments explaining why columns are hardcoded.

Error Handling

• Handle NULLs: Replace NULL with 0 or COALESCE: sql COALESCE(SUM(CASE WHEN ... END), 0) AS Tech
• Test edge cases: What if a category is missing? What if order_date is NULL?

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Given a table, write a pivot query.
2. Trick: They’ll test if you remember GROUP BY and aggregation.
3. Compare CASE vs PIVOT.
4. Key distinction: CASE is universal; PIVOT is database-specific.
5. Dynamic pivot scenario.
6. Trap: They’ll ask for a dynamic pivot but expect you to explain the risks (SQL injection, complexity).

Example Question

"You have a table employee_salaries with columns employee_id, department, and salary. Write a query to show the average salary per department, pivoted by gender (M/F)."

Answer:

SELECT
    department,
    AVG(CASE WHEN gender = 'M' THEN salary ELSE NULL END) AS avg_male_salary,
    AVG(CASE WHEN gender = 'F' THEN salary ELSE NULL END) AS avg_female_salary
FROM employee_salaries
GROUP BY department;

7. ? Hands-On Challenge

Challenge:
You have a table student_grades:

student_id | subject | grade
1          | Math    | 90
1          | Science | 85
2          | Math    | 75

Write a query to pivot this into:

student_id | Math | Science
1          | 90   | 85
2          | 75   | NULL

Solution:

SELECT
    student_id,
    MAX(CASE WHEN subject = 'Math' THEN grade ELSE NULL END) AS Math,
    MAX(CASE WHEN subject = 'Science' THEN grade ELSE NULL END) AS Science
FROM student_grades
GROUP BY student_id;

Why it works: MAX() collapses multiple rows into one per student, ignoring NULL values.

8. ? Rapid-Reference Crib Sheet

• CASE syntax:
  sql CASE WHEN condition THEN value ELSE default END
• PIVOT syntax (SQL Server):
  sql SELECT * FROM table PIVOT (SUM(column) FOR pivot_column IN ([value1], [value2])) AS PivotTable;
• Aggregation functions: SUM(), COUNT(), AVG(), MAX().
• GROUP BY: Must include all non-aggregated columns.
• ⚠️ NULL handling: Use COALESCE(column, 0) or ISNULL(column, 0).
• Dynamic pivots: Require dynamic SQL (risky; avoid in production unless necessary).

9. ? Where to Go Next

1. PostgreSQL CASE Documentation
2. SQL Server PIVOT Guide
3. BigQuery Pivoting
4. Book: "SQL for Data Analysis" by O’Reilly (Chapter 5: Pivoting Data).