SQL PIVOT and UNPIVOT Operations

🔹 What Are PIVOT and UNPIVOT?

PIVOT converts rows into columns, often to create cross-tab reports, while UNPIVOT converts columns back into rows for data normalization. They are invaluable in reporting, dashboards, and analytics workflows.

⚙️ Why They Matter

Reduce custom CASE statements for cross-tab layouts.
Summarize transactional data into friendly reporting formats.
Normalize wide tables for loading into fact tables or visualization tools.

🧠 Example 1 – Native PIVOT (SQL Server / Oracle)

SELECT *
FROM (
  SELECT Product, Quarter, Amount
  FROM Sales
) AS SourceTable
PIVOT (
  SUM(Amount)
  FOR Quarter IN ([Q1], [Q2])
) AS PivotTable;

📘 Rows (Q1, Q2) become columns, showing totals per product per quarter.

🧠 Example 2 – Manual PIVOT in PostgreSQL/MySQL

SELECT
  Product,
  SUM(CASE WHEN Quarter = 'Q1' THEN Amount ELSE 0 END) AS Q1,
  SUM(CASE WHEN Quarter = 'Q2' THEN Amount ELSE 0 END) AS Q2
FROM Sales
GROUP BY Product;

💡 The CASE + GROUP BY pattern works universally when native PIVOT is unavailable.

🧠 Example 3 – UNPIVOT (Reverse Transformation)

SELECT Product, Quarter, Amount
FROM SalesPivot
UNPIVOT (
  Amount FOR Quarter IN ([Q1], [Q2])
) AS UnpivotTable;

📘 Converts columns back into row values for easier analysis or loading into fact tables.

⚙️ Monthly Salary Pivot

SELECT *
FROM (
  SELECT emp_id, month, salary
  FROM salaries
) AS s
PIVOT (
  SUM(salary) FOR month IN ([Jan], [Feb], [Mar], [Apr])
) AS SalaryPivot;

📘 Produces a compact salary grid—one row per employee, months as columns.

⚖️ Comparison Table

Operation	Transforms	Common Use Cases	Supported In
PIVOT	Rows → Columns	Dashboards, KPI scorecards, cross-tab summaries	SQL Server, Oracle (native)
UNPIVOT	Columns → Rows	Data warehousing, ETL, normalization	SQL Server, Oracle (native)
Manual PIVOT	Rows → Columns	Systems without `PIVOT`	PostgreSQL, MySQL via `CASE` logic

💡 Interview Soundbite

“Use PIVOT to rotate rows into columns for reporting, and UNPIVOT to normalize wide tables. When native syntax is missing, simulate it with CASE expressions.”

← Previous Next →

← Back to Articles | 🏠 Back to Homepage