SQL
Transform Rows to Columns with a SQL Pivot Query
Master the technique of pivoting data in SQL, transforming unique values from one column into new distinct columns, often used for creating summary reports.
-- Sample data: Daily sales for different products
-- CREATE TABLE daily_sales (sale_date DATE, product_name VARCHAR(50), sales_amount DECIMAL(10, 2));
-- INSERT INTO daily_sales VALUES
-- ('2023-01-01', 'Product A', 100.00),
-- ('2023-01-01', 'Product B', 150.00),
-- ('2023-01-02', 'Product A', 120.00),
-- ('2023-01-02', 'Product C', 200.00),
-- ('2023-01-03', 'Product B', 180.00);
SELECT
sale_date,
SUM(CASE WHEN product_name = 'Product A' THEN sales_amount ELSE 0 END) AS "Product A Sales",
SUM(CASE WHEN product_name = 'Product B' THEN sales_amount ELSE 0 END) AS "Product B Sales",
SUM(CASE WHEN product_name = 'Product C' THEN sales_amount ELSE 0 END) AS "Product C Sales"
FROM
daily_sales
GROUP BY
sale_date
ORDER BY
sale_date;
How it works: This SQL snippet demonstrates how to "pivot" data, transforming unique values from a row-based column (e.g., `product_name`) into distinct columns in the result set. It uses conditional aggregation with `SUM(CASE WHEN ... THEN ... ELSE 0 END)` to aggregate `sales_amount` for each specific `product_name` on a given `sale_date`. This technique is extremely useful for creating cross-tabulation reports where you want to see summary values across categories as columns.