SQL
Create Pivot-like Reports with Conditional Aggregation in SQL
Transform rows into columns for data analysis by using `CASE` statements within aggregate functions to build pivot-like reports without a dedicated `PIVOT` clause.
-- Example: Calculate total sales for each product category per month
SELECT
category,
SUM(CASE WHEN DATE_FORMAT(sale_date, '%Y-%m') = '2023-01' THEN amount ELSE 0 END) AS sales_jan_2023,
SUM(CASE WHEN DATE_FORMAT(sale_date, '%Y-%m') = '2023-02' THEN amount ELSE 0 END) AS sales_feb_2023,
SUM(CASE WHEN DATE_FORMAT(sale_date, '%Y-%m') = '2023-03' THEN amount ELSE 0 END) AS sales_mar_2023
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY category
ORDER BY category;
-- Note: DATE_FORMAT is for MySQL. Use TO_CHAR(sale_date, 'YYYY-MM') for PostgreSQL/Oracle or STRFTIME('%Y-%m', sale_date) for SQLite.
How it works: This snippet demonstrates conditional aggregation to create a pivot-like report. Instead of using a specific `PIVOT` clause (which varies or isn't available in all SQL databases), it employs `CASE` statements within `SUM()` aggregate functions. For each `category`, it sums the `amount` only when the `sale_date` falls within a specific month, effectively creating columns for each month's sales. This technique is highly flexible for transforming row-based data into a summary format.