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]

---

📈 Learning Path

• ← Back: Set Operators
• Next: GROUP BY & Analytics →