SQL
Generate Cross-Tabulation Reports with Conditional Aggregation
Learn to create flexible summary reports in SQL by aggregating data based on conditions, simulating pivot table behavior without a dedicated PIVOT function.
SELECT
product_category,
SUM(CASE WHEN order_status = 'Pending' THEN 1 ELSE 0 END) AS total_pending,
SUM(CASE WHEN order_status = 'Shipped' THEN 1 ELSE 0 END) AS total_shipped,
SUM(CASE WHEN order_status = 'Delivered' THEN 1 ELSE 0 END) AS total_delivered,
COUNT(*) AS grand_total_orders
FROM
orders
GROUP BY
product_category
ORDER BY
product_category;
How it works: This snippet demonstrates how to generate cross-tabulated reports using conditional aggregation. By embedding CASE statements within aggregate functions like SUM or COUNT, you can count or sum values based on specific conditions for different columns. This technique is highly flexible for creating custom reports, allowing you to effectively 'pivot' data and display summarized information across various categories or statuses without relying on database-specific PIVOT syntax, which might not be universally available or performant.