SQL

Pivoting Data from Rows to Columns with Conditional Aggregation

Transform data from a vertical format to a horizontal summary in SQL using conditional aggregation (SUM with CASE WHEN), essential for creating dynamic reports and cross-tabulations.

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 SQL snippet demonstrates how to pivot data from a row-based format into columns using conditional aggregation. It utilizes `SUM(CASE WHEN ... THEN ... END)` to create new columns (e.g., Sales_North, Sales_South) for each distinct region's sales, grouped by product category. This technique is highly effective for generating summary reports where specific attribute values become column headers.

Pivoting Data from Rows to Columns with Conditional Aggregation

Need help integrating this into your project?