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