Transform Rows to Columns with SQL Conditional Aggregation (Pivoting)

SELECT
    customer_id,
    COUNT(CASE WHEN order_status = 'pending' THEN order_id ELSE NULL END) AS pending_orders,
    COUNT(CASE WHEN order_status = 'completed' THEN order_id ELSE NULL END) AS completed_orders,
    COUNT(CASE WHEN order_status = 'cancelled' THEN order_id ELSE NULL END) AS cancelled_orders,
    COUNT(order_id) AS total_orders
FROM
    orders
GROUP BY
    customer_id
ORDER BY
    customer_id;

How it works: This snippet demonstrates conditional aggregation, a technique used to 'pivot' data, transforming row values into column headers. By using a `CASE` expression inside an aggregate function like `COUNT()`, we can count or sum values based on specific conditions. For each `customer_id`, this query counts orders with a 'pending', 'completed', or 'cancelled' status, effectively showing a breakdown of order statuses per customer in a single row, which is highly useful for summary reports and dashboards.