Using PIVOT and UNPIVOT in SQL Server
💡 Concept Name
PIVOT transforms rows into columns, while UNPIVOT does the reverse—converting columns into rows in SQL Server.
📘 Quick Intro
PIVOT helps reshape tabular data for better reporting, allowing values in a column to become new columns. UNPIVOT reverses that, turning columns back into rows to normalize or analyze the data further.
🧠 Analogy / Short Story
Imagine a spreadsheet with monthly sales listed vertically for each product. PIVOT flips that layout—months become columns, and products stay in rows. UNPIVOT turns it back to vertical form. It’s like rotating your spreadsheet view to see patterns differently.
🔧 Technical Explanation
- PIVOT aggregates data and turns unique values in one column into multiple columns.
- UNPIVOT takes columns and converts them into rows under a single column name.
PIVOTmust be applied on an aggregate function likeSUM,AVG, etc.UNPIVOTis useful for denormalizing wide tables into tall, analyzable formats.- Requires derived tables or CTEs to pre-select and format source data.
🎯 Purpose & Use Case
- ✅ Creating dynamic reports and dashboards.
- ✅ Transforming normalized data for visualization.
- ✅ Simplifying comparisons across multiple columns.
- ✅ Reformatting ETL pipeline outputs.
💻 Real Code Example
-- Sample PIVOT
SELECT *
FROM (
SELECT Product, Month, Sales
FROM SalesData
) AS SourceTable
PIVOT (
SUM(Sales)
FOR Month IN ([Jan], [Feb], [Mar])
) AS PivotTable;
-- Sample UNPIVOT
SELECT Product, Month, Sales
FROM (
SELECT Product, Jan, Feb, Mar
FROM SalesSummary
) AS SourceTable
UNPIVOT (
Sales FOR Month IN ([Jan], [Feb], [Mar])
) AS Unpivoted;
❓ Interview Q&A
Q1: What does the PIVOT operator do?
A: It turns row values into column headers with aggregated data.
Q2: What does UNPIVOT do?
A: It transforms columns back into rows for each value.
Q3: Can you use PIVOT without aggregation?
A: No, PIVOT requires an aggregate function like SUM or COUNT.
Q4: What is a prerequisite for PIVOT/UNPIVOT?
A: You need a derived table or subquery as the source.
Q5: Is UNPIVOT helpful for normalization?
A: Yes, it helps convert wide tables into normalized structures.
Q6: Which clause lists new column names in PIVOT?
A: The IN clause after FOR column.
Q7: Can PIVOT handle dynamic column names?
A: Not directly—you need dynamic SQL for that.
Q8: What’s a use case for PIVOT?
A: Creating monthly sales reports with months as columns.
Q9: What’s a use case for UNPIVOT?
A: Flattening product-wise sales data for analysis.
Q10: Which SQL Server version introduced PIVOT/UNPIVOT?
A: SQL Server 2005.
📝 MCQs
Q1. What is the purpose of the PIVOT operator?
- Join tables
- Filter rows
- Convert rows into columns
- Update records
Q2. Which function is mandatory in PIVOT?
- Scalar function
- Aggregate function
- Window function
- String function
Q3. What does UNPIVOT convert?
- Tables into views
- Columns into rows
- Views into tables
- Rows into constraints
Q4. Can PIVOT be used with GROUP BY?
- Yes, always
- No, it replaces GROUP BY
- Only with HAVING
- Only with joins
Q5. Which clause defines column headers in PIVOT?
- SELECT
- GROUP BY
- IN clause
- WHERE
Q6. Is a subquery needed for PIVOT?
- No
- Yes
- Only in MySQL
- Only with views
Q7. What type of report is PIVOT ideal for?
- Flat-file
- ETL
- Cross-tab
- Stored procedure
Q8. Which SQL version introduced PIVOT?
- SQL Server 2000
- SQL Server 2005
- SQL Server 2012
- SQL Server 2016
Q9. Can UNPIVOT be used to normalize data?
- No
- Yes
- Only in Python
- Only with Excel
Q10. What is the output of UNPIVOT?
- Wide and short table
- Flat JSON
- Tall and narrow table
- Grouped rows
💡 Bonus Insight
Dynamic PIVOT requires dynamic SQL because column names in the IN clause must be known at compile time. Use STUFF() and FOR XML PATH techniques to generate dynamic columns in advanced reports.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!