What are PIVOT and UNPIVOT in T-SQL?
Quick Answer
PIVOT rotates rows into columns — turning a list of (Month, Amount) rows into one row with a column per month. UNPIVOT does the reverse, turning wide columns back into rows. Many developers prefer conditional aggregation (SUM(CASE WHEN Month = 'Jan' THEN Amount END)) because it's more flexible.
PIVOT turns rows into columns — say you have monthly sales as rows and you want one row per product with a column for each month. UNPIVOT goes the other way. Honestly I usually skip the PIVOT keyword and use conditional aggregation instead — SUM(CASE WHEN month = 'Jan' THEN amount END) per column — because it reads more clearly and it's easier when I need to add a computed column alongside. The one hard part of pivoting is when you don't know the column values in advance; then you need dynamic SQL to build the statement.
PIVOTneeds you to list the output column names, so it's awkward when the values aren't known ahead of time — that requires dynamic SQL to build the column list.- Conditional aggregation:
SELECT Category, SUM(CASE WHEN Year=2025 THEN Amt END) AS y2025, SUM(CASE WHEN Year=2026 THEN Amt END) AS y2026 FROM ... GROUP BY Category;— same result, easier to read and extend. - Reporting/BI tools usually pivot on the client side; do it in SQL when the consumer needs a fixed-shape result.