SQL

Pivot Data from Rows to Columns with Conditional Aggregation

Transform your row-based data into a cross-tabular report by pivoting values from a specific column into multiple new columns using SQL's CASE statements and aggregate functions.

SELECT
  product_name,
  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
  regional_sales
GROUP BY
  product_name
ORDER BY
  product_name;

How it works: This query pivots data by transforming distinct values from the `region` column into separate columns (`sales_north`, `sales_south`, etc.). It uses `CASE` statements within `SUM` aggregate functions to conditionally sum `sales_amount` for each region. The `GROUP BY product_name` clause ensures that sales for each product across different regions are aggregated into a single row, effectively creating a cross-tabular report.

Pivot Data from Rows to Columns with Conditional Aggregation

Need help integrating this into your project?