Pivot with CASE in SQL
Level 5288 keystrokes
CASE inside aggregate functions creates pivot table-like output.
SQL code
-- One row per product, one column per month1SELECT2 product_name, -- Conditional sum: add quantity only for January sales3 SUM( -- MONTH() gives 1-12; other months add 04 CASE WHEN MONTH(sale_date) = 15 THEN quantity ELSE 0 END6 ) AS jan, -- Same pattern for February7 SUM(8 CASE WHEN MONTH(sale_date) = 29 THEN quantity ELSE 0 END10 ) AS feb, -- ...and for March11 SUM(12 CASE WHEN MONTH(sale_date) = 313 THEN quantity ELSE 0 END14 ) AS mar-- No year filter, so the same month of different years is added together15FROM16 sales-- One group, and one output row, per product17GROUP BY18 product_name-- Sort alphabetically by product name19ORDER BY20 product_name;Output
+--------------+-----+-----+-----+ | product_name | jan | feb | mar | +--------------+-----+-----+-----+ | Laptop | 10 | 15 | 12 | | Phone | 25 | 30 | 28 | | Tablet | 8 | 10 | 15 | +--------------+-----+-----+-----+ 3 rows in set
Practice typing this code. Results are not recorded in the rankings.
Symbols in this code
The symbols you type in this SQL code and how often they appear.
_× 6(× 6)× 6,× 3=× 3;× 1