Oracle PIVOT & UNPIVOT 📊¶

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]