SQL
Generate Pivot-Like Reports Using SQL Conditional Aggregation
Discover how to transform rows into columns using SQL's conditional aggregation with `CASE` expressions inside aggregate functions like `SUM` or `COUNT`, creating powerful pivot-like reports.
SELECT
order_id,
SUM(CASE WHEN item_type = 'Product' THEN quantity ELSE 0 END) AS total_products,
SUM(CASE WHEN item_type = 'Service' THEN quantity ELSE 0 END) AS total_services,
COUNT(CASE WHEN item_status = 'Pending' THEN 1 ELSE NULL END) AS pending_items,
COUNT(CASE WHEN item_status = 'Completed' THEN 1 ELSE NULL END) AS completed_items
FROM
order_items
GROUP BY
order_id
ORDER BY
order_id;
-- Aggregates item quantities and counts based on conditions into new columns
How it works: This snippet illustrates conditional aggregation, a technique to "pivot" data without using specific `PIVOT` clauses (which might not be available in all SQL dialects). By embedding `CASE` expressions within aggregate functions like `SUM()` or `COUNT()`, you can count or sum values based on specific conditions, effectively transforming distinct row values into separate columns for a more summarized, report-friendly view of your data per group (e.g., `order_id`).