SQL Pivot and Crosstab Queries

-- Pivot with CASE expressions (works in all databases)
SELECT
    product,
    SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS jan,
    SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS feb,
    SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS mar,
    SUM(revenue) AS total
FROM monthly_sales
GROUP BY product
ORDER BY total DESC;

-- Pivot with FILTER clause (PostgreSQL)
SELECT
    region,
    COUNT(*) FILTER (WHERE status = 'active') AS active,
    COUNT(*) FILTER (WHERE status = 'inactive') AS inactive,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending,
    COUNT(*) AS total
FROM users
GROUP BY region;

-- Unpivot (turn columns into rows)
SELECT product, 'Jan' AS month, jan AS revenue FROM pivot_table
UNION ALL
SELECT product, 'Feb', feb FROM pivot_table
UNION ALL
SELECT product, 'Mar', mar FROM pivot_table
ORDER BY product, month;

-- Dynamic-style pivot with JSON aggregation
SELECT
    user_id,
    jsonb_object_agg(setting_key, setting_value) AS settings
FROM user_settings
GROUP BY user_id;

-- Cross-tabulation: users per role per department
SELECT
    department,
    COUNT(*) FILTER (WHERE role = 'engineer') AS engineers,
    COUNT(*) FILTER (WHERE role = 'designer') AS designers,
    COUNT(*) FILTER (WHERE role = 'manager') AS managers
FROM employees
GROUP BY department
ORDER BY department;