SQL
Conditional Aggregation for Pivot-like Reporting
Perform flexible pivot-like aggregations in SQL using CASE statements within aggregate functions like SUM() or COUNT(), enabling powerful custom reporting without PIVOT.
SELECT
order_date,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
COUNT(order_id) AS total_orders
FROM orders
GROUP BY order_date
ORDER BY order_date;
How it works: This snippet demonstrates conditional aggregation, a versatile technique to generate pivot-like reports without using specific `PIVOT` syntax. By embedding `CASE` statements inside `SUM()` or `COUNT()` functions, you can count or sum values based on specific conditions within each group (e.g., `order_date`). This allows for dynamic column creation based on attribute values, providing summary statistics for different categories in a single row.