SQL PATTERNS : Pivot and Unpivot in SQL - Transforming Data Effectively

📌 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! ⬇️

Comments

[Aaron Reese]

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.

[Anwar]

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,

1. One approach as you said would be a concatenation technique (STR_AGG or STUFF...FOR XML)
2. Instead of pivoting dynamically at runtime, a Materialized View or Scheduled Job can precompute pivoted data and store it in a table.

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.