SQL
Pivot Data Using Conditional Aggregation
Transform row-level data into columns using conditional aggregation with CASE statements, useful for creating summary reports with fixed categories.
SELECT
product_category,
SUM(CASE WHEN region = 'North' THEN sales_amount ELSE 0 END) AS sales_north,
SUM(CASE WHEN region = 'South' THEN sales_amount ELSE 0 END) AS sales_south,
SUM(CASE WHEN region = 'East' THEN sales_amount ELSE 0 END) AS sales_east,
SUM(CASE WHEN region = 'West' THEN sales_amount ELSE 0 END) AS sales_west
FROM
sales_data
GROUP BY
product_category
ORDER BY
product_category;
How it works: This snippet demonstrates how to 'pivot' data, transforming unique values from one column (e.g., 'region') into separate columns. It achieves this using conditional aggregation with `SUM` and `CASE` statements. For each `product_category`, it sums `sales_amount` only for records belonging to a specific region, effectively creating a cross-tabulated report. This is a common technique for generating summary reports where the number of pivot columns is known.