SQL
Pivot Rows to Columns Using Conditional Aggregation in SQL
Learn how to transform data from a vertical, row-based format into a horizontal, column-based format for better readability and reporting in SQL using CASE statements.
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 query demonstrates how to "pivot" data, transforming unique values from a column (e.g., `region`) into distinct columns in the result set. It uses `CASE` statements within aggregate functions (`SUM`) to conditionally sum `sales_amount` for each region, grouped by `product_category`. This technique is highly useful for generating summary reports where you need specific attributes as columns.