SQL
Perform Dynamic Pivoting with Conditional Aggregation
Transform row-based data into a columnar pivot table format for reporting using conditional aggregation with CASE statements inside aggregate functions.
-- Example Table: monthly_sales (sale_id, product_name, sale_month, amount)
-- Sample data:
-- (1, 'Laptop', 'Jan', 1200)
-- (2, 'Mouse', 'Jan', 25)
-- (3, 'Laptop', 'Feb', 1300)
-- (4, 'Keyboard', 'Feb', 75)
-- (5, 'Mouse', 'Mar', 30)
-- Pivot sales data to show total amount per product for each month
SELECT
product_name,
SUM(CASE WHEN sale_month = 'Jan' THEN amount ELSE 0 END) AS Total_Jan_Sales,
SUM(CASE WHEN sale_month = 'Feb' THEN amount ELSE 0 END) AS Total_Feb_Sales,
SUM(CASE WHEN sale_month = 'Mar' THEN amount ELSE 0 END) AS Total_Mar_Sales,
SUM(amount) AS Grand_Total
FROM
monthly_sales
GROUP BY
product_name
ORDER BY
product_name;
-- This technique can also be used with COUNT, AVG, MAX, MIN aggregates.
How it works: Conditional aggregation is a flexible technique to 'pivot' data, transforming unique values from one column into separate columns. This snippet shows how to sum sales `amount` for each `product_name` across different `sale_month` values. It uses `SUM(CASE WHEN condition THEN value ELSE 0 END)` to conditionally include `amount` in the sum based on the `sale_month`. The `GROUP BY product_name` then aggregates these sums for each product, effectively creating a pivot table where months are columns.