SQL
Generate Cross-Tabular Reports with Conditional Aggregation
Transform row data into column summaries using conditional aggregation with `CASE` expressions within aggregate functions for dynamic cross-tabular reports.
SELECT
product_category,
SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN quantity_sold ELSE 0 END) AS JanuarySales,
SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN quantity_sold ELSE 0 END) AS FebruarySales,
SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN quantity_sold ELSE 0 END) AS MarchSales
FROM sales
GROUP BY product_category
ORDER BY product_category;
How it works: This query creates a pivot-like report by using `CASE` expressions within `SUM` aggregate functions. It groups sales data by `product_category` and then conditionally sums `quantity_sold` for specific months, effectively transforming monthly sales data from rows into distinct columns for each month, offering a clear cross-tabular view for reporting dashboards.