Q38: How do you perform PIVOT and UNPIVOT transformations in SQL?
A PIVOT converts unique row values into individual query output columns (transposing data from vertical to horizontal). An UNPIVOT converts columns back into row records.
While databases like SQL Server support native PIVOT keywords, cross-database code uses conditional aggregation (CASE WHEN).
Cross-Platform Pivoting via Conditional Aggregation
SELECT
department_id,
SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS q1_sales,
SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS q2_sales,
SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) AS q3_sales,
SUM(CASE WHEN quarter = 'Q4' THEN sales ELSE 0 END) AS q4_sales
FROM department_sales
GROUP BY department_id;💡 Conditional aggregation aggregates row attributes into horizontal column buckets across all standard SQL engines.
Key Takeaways
- Conditional aggregation converts vertical row values into horizontal column sets.
- Supported across all major relational database engines without platform-specific extensions.