Intermediate LevelSQL & MySQL
How do PIVOT and UNPIVOT operations transform rows into columns?
❓ Interview Prompt: Explain PIVOT and UNPIVOT transformations, dynamic conditional aggregation (`MAX(CASE WHEN ...)`), and rotating datasets.
PIVOT transforms row data into a columnar cross-tabulation format, while UNPIVOT rotates columns back into rows:
Cross-Tabulation via Conditional Aggregation:
In databases lacking a native 'PIVOT' keyword (like MySQL), pivoting is achieved using 'SUM(CASE ...)' or 'MAX(CASE ...)' combined with 'GROUP BY'.
Code Implementation Example
sql
-- Pivoting monthly sales rows into quarterly columns using CASE aggregation
SELECT
year,
SUM(CASE WHEN month IN (1, 2, 3) THEN amount ELSE 0 END) AS Q1_Sales,
SUM(CASE WHEN month IN (4, 5, 6) THEN amount ELSE 0 END) AS Q2_Sales,
SUM(CASE WHEN month IN (7, 8, 9) THEN amount ELSE 0 END) AS Q3_Sales,
SUM(CASE WHEN month IN (10, 11, 12) THEN amount ELSE 0 END) AS Q4_Sales
FROM monthly_sales
GROUP BY year;💡Pivoting monthly sales rows into quarterly report columns using conditional aggregation.
🎯 Interview Key Takeaway
Use conditional aggregation (SUM/CASE) to pivot row data into columnar summary reports.
#SQL#PIVOT#UNPIVOT#Aggregation#Intermediate
Back to all SQL & MySQL Questions→