📌 Introduction
Handling data transformations is a crucial skill in SQL, especially for reporting and analytics. PIVOT and UNPIVOT operations help in restructuring data efficiently:
🔹 PIVOT: Converts row-based data into a column-based format.
🔹 UNPIVOT: Converts column-based data into row-based format.
In this article, we will explore both operations with real-world examples.
🛠 Understanding PIVOT in SQL
Scenario: You have sales data in a normalized format where each row represents sales for a specific month. You want to convert this into a columnar format to generate reports.
📌 Example: PIVOT Sales Data by Month
📊 Sample Table: Sales
| Product | Month | Sales |
|---|---|---|
| Laptop | Jan | 5000 |
| Laptop | Feb | 7000 |
| Laptop | Mar | 8000 |
| Phone | Jan | 3000 |
| Phone | Feb | 4500 |
| Phone | Mar | 5000 |
🔍 PIVOT Query
SELECT *
FROM (
SELECT Product, Month, Sales
FROM Sales
) AS SourceTable
PIVOT (
SUM(Sales)
FOR Month IN ([Jan], [Feb], [Mar])
) AS PivotTable;
✅ Output
| Product | Jan | Feb | Mar |
|---|---|---|---|
| Laptop | 5000 | 7000 | 8000 |
| Phone | 3000 | 4500 | 5000 |
🧐 Explanation
- We used
SUM(Sales)to aggregate values. - The FOR Month IN
([Jan], [Feb], [Mar])clause dynamically transforms row values into column headers.
🔄 Understanding UNPIVOT in SQL
Scenario: You receive a dataset where sales are already pivoted by months, but you need to transform it back into a normalized row-based format.
📌 Example: UNPIVOT Sales Data
📊 Pivoted Table: Sales_Pivoted
| Product | Jan | Feb | Mar |
|---|---|---|---|
| Laptop | 5000 | 7000 | 8000 |
| Phone | 3000 | 4500 | 5000 |
🔍 UNPIVOT Query
SELECT Product, Month, Sales
FROM (
SELECT Product, Jan, Feb, Mar
FROM Sales_Pivoted
) AS PivotTable
UNPIVOT (
Sales FOR Month IN (Jan, Feb, Mar)
) AS UnpivotTable;
✅ Output
| Product | Month | Sales |
|---|---|---|
| Laptop | Jan | 5000 |
| Laptop | Feb | 7000 |
| Laptop | Mar | 8000 |
| Phone | Jan | 3000 |
| Phone | Feb | 4500 |
| Phone | Mar | 5000 |
🧐 Explanation
The UNPIVOT operator converts multiple column values (Jan, Feb, Mar) back into row values under the Month column.
This makes it easier to analyze or join with other normalized tables.
📌 When to Use PIVOT and UNPIVOT?
| Use Case | Operation |
|---|---|
| Convert rows into columns (e.g., monthly reports) | PIVOT |
| Convert columns into rows (e.g., normalizing data) | UNPIVOT |
🚀 Conclusion
PIVOT and UNPIVOT are powerful SQL techniques that help in data transformation for better reporting and analysis. Mastering them will enable you to handle structured data more effectively.
💡 What are your thoughts?
Have you used PIVOT and UNPIVOT in your SQL queries? Let’s discuss in the comments! ⬇️
Top comments (2)
The main frustration with PIVOT and UNPIVOT is you need to know the values in the column that you want to effect the command on. In your example you have effected on Month which is a known limited list. If you wanted to effect on Product this lost could change over time. The only way to solve this is to create a dynamic query: use a concatenation technique (STR_AGG or STUFF...FOR XML) to generate a list of values and then build the SQL statement into a variable which you can then call with EXEC sp_executeSql @sql
PIVOTed data is not usable in a view because the column data is not fixed.
You're absolutely right! The main challenge with PIVOT and UNPIVOT is that they require a fixed set of column values, making them less flexible for dynamic datasets where values change over time (e.g., Product names).
To handle this dynamically,
Takeaways
✅ Static PIVOTs work best with known, limited values (e.g., Months).
✅ For dynamic data (e.g., Products, dynamic categories), use dynamic SQL with STRING_AGG or STUFF...FOR XML.
✅ Views do not support dynamic PIVOTs, but Stored Procedures provide a flexible alternative.