rag/sample_templates/14_pivoting.sql

-- Template: Pivoting Data (CASE with Aggregation)
-- Pattern: Transform rows into columns
-- Use Case: Crosstab reports, monthly summaries, category breakdowns

-- Example 1: Revenue by month in columns
SELECT 
    EXTRACT(YEAR FROM order_date) as year,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN total_amount ELSE 0 END) as jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN total_amount ELSE 0 END) as feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN total_amount ELSE 0 END) as mar,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 4 THEN total_amount ELSE 0 END) as apr,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 5 THEN total_amount ELSE 0 END) as may,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 6 THEN total_amount ELSE 0 END) as jun,
    SUM(total_amount) as total_year
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;

-- Example 2: Customer count by status and city
SELECT 
    city,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_customers,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive_customers,
    SUM(CASE WHEN status = 'vip' THEN 1 ELSE 0 END) as vip_customers,
    COUNT(*) as total_customers
FROM customers
GROUP BY city
ORDER BY total_customers DESC;

-- Key Concepts:
-- - CASE WHEN inside aggregate function
-- - Each CASE creates a new column
-- - SUM with 0/1 for counts, SUM with value for totals
-- - Alternative: Use FILTER (WHERE) in PostgreSQL
-- - Useful for summary reports and dashboards