SQL
Transform Rows to Columns (Pivot Data) with SQL `CASE` Statements
Learn to dynamically pivot data in SQL, converting unique row values into distinct columns using conditional aggregation with `SUM()` and `CASE` statements for clearer reporting.
SELECT
product_category,
SUM(CASE WHEN month = 'Jan' THEN sales_amount ELSE 0 END) AS Jan_Sales,
SUM(CASE WHEN month = 'Feb' THEN sales_amount ELSE 0 END) AS Feb_Sales,
SUM(CASE WHEN month = 'Mar' THEN sales_amount ELSE 0 END) AS Mar_Sales,
-- ... add more months as needed
SUM(sales_amount) AS Total_Sales_Q1
FROM
monthly_sales
GROUP BY
product_category
ORDER BY
product_category;
-- Assume monthly_sales table structure:
-- CREATE TABLE monthly_sales (
-- product_category VARCHAR(50),
-- month VARCHAR(3), -- e.g., 'Jan', 'Feb', 'Mar'
-- sales_amount DECIMAL(10, 2)
-- );
How it works: Pivoting data transforms rows into columns, which is essential for creating summary reports. This SQL snippet achieves pivoting using `SUM()` with `CASE` statements. For each `product_category`, it checks the `month` value and conditionally adds `sales_amount` to the corresponding monthly sales column (e.g., `Jan_Sales`). This technique aggregates data across rows and presents it in a cross-tabulated format, making it easier to compare sales performance across categories for different months at a glance.