sql / expert
Snippet
Relational Transformation via Aggregate CASE Expressions
Simulates a pivot operation by combining CASE logic with aggregate functions. This transforms row-based data into a columnar matrix format for cross-tabulation reports.
snippet.sql
1
2
3
4
5
6
7
8
SELECTstore_id,SUM(CASE WHEN quarter = 1 THEN revenue ELSE 0 END) AS Q1_Revenue,SUM(CASE WHEN quarter = 2 THEN revenue ELSE 0 END) AS Q2_Revenue,SUM(CASE WHEN quarter = 3 THEN revenue ELSE 0 END) AS Q3_Revenue,SUM(CASE WHEN quarter = 4 THEN revenue ELSE 0 END) AS Q4_RevenueFROM sales_dataGROUP BY store_id;
Breakdown
1
SUM(CASE WHEN quarter = 1 THEN revenue ELSE 0 END)
Conditional aggregation that only adds revenue to the sum if the row belongs to the first quarter.
2
GROUP BY store_id
Collapses all quarter rows into a single row per store.