SQL
Pivot Rows to Columns Using Conditional Aggregation
Transform your data from rows into columns, or "pivot" it, using SQL's conditional aggregation with `CASE` statements for better reporting.
SELECT
employee_id,
SUM(CASE WHEN month = 'Jan' THEN sales_amount ELSE 0 END) AS JanSales,
SUM(CASE WHEN month = 'Feb' THEN sales_amount ELSE 0 END) AS FebSales,
SUM(CASE WHEN month = 'Mar' THEN sales_amount ELSE 0 END) AS MarSales,
SUM(CASE WHEN month = 'Apr' THEN sales_amount ELSE 0 END) AS AprSales,
-- ... add more months as needed
SUM(sales_amount) AS TotalSales
FROM
monthly_sales
GROUP BY
employee_id
ORDER BY
employee_id;
-- Assuming 'monthly_sales' table structure:
-- CREATE TABLE monthly_sales (
-- employee_id INT,
-- month VARCHAR(3), -- e.g., 'Jan', 'Feb', 'Mar'
-- sales_amount DECIMAL(10, 2)
-- );
How it works: This snippet demonstrates a common SQL technique called "pivoting" data, where rows are transformed into columns. It uses `SUM` with `CASE` expressions (conditional aggregation) to group sales by `employee_id` and then create separate columns for each month's sales. This approach is highly useful for generating summary reports where you want to see distinct categories (like months) as columns rather than rows, providing a more readable and analytical view of your data.