Oracle PIVOT & UNPIVOT 📊¶
Prerequisites: Aggregates, GROUP BY
Mentor's Note: Have you ever used a "Pivot Table" in Excel? Oracle has the exact same power. You can turn "Sales by Month" into "One Column for each Month" automatically! 💡
🌟 The Scenario: The Sales Report 📈¶
You have sales data in rows: - Item: Pen, Month: Jan, Total: 100 - Item: Pen, Month: Feb, Total: 150 - PIVOT: Turn this into a clean table with columns: [Item] | [Jan Sales] | [Feb Sales].
💻 1. The PIVOT Operator (Rows to Columns)¶
Converts data from a long format into a wide "Crosstab" format.
SELECT * FROM (
SELECT job_id, department_id, salary FROM employees
)
PIVOT (
SUM(salary) -- The value
FOR department_id IN (10 AS "Admin", 20 AS "Marketing", 30 AS "IT") -- The headers
);
💻 2. The UNPIVOT Operator (Columns to Rows)¶
The reverse of PIVOT. It takes wide data and makes it "Narrow" for better processing.
SELECT product_id, quarter, sales_amount
FROM quarterly_sales
UNPIVOT (
sales_amount FOR quarter IN (Q1, Q2, Q3, Q4)
);
🏗️ Architect's Note: Analytics Performance 🛡️¶
PIVOT and UNPIVOT were introduced in Oracle 11g.
- The Architect's Secret: Internally, PIVOT is a complex CASE statement combined with a GROUP BY. Oracle has optimized it heavily, but it is still CPU-intensive.
- Tip: Always filter your inner query (the source data) as much as possible before applying the PIVOT to save memory!
🎨 Visual Logic: Data Rotation¶
PIVOT:
[Date | Sales] --> [Jan | Feb | Mar]
[100 | 150 | 200]
UNPIVOT:
[Jan | Feb | Mar] --> [Month | Sales]
[Jan | 100]
[Feb | 150]