12. PIVOT and UNPIVOT

Sometimes, we need to find a specific column from the first table by comparing it with multiple columns from the second table. If we use a JOIN query for this, it can be very slow, especially when dealing with more than half a million records.

To solve this, we can convert five columns into rows. This makes the JOIN operation much faster because it will match only one column instead of multiple columns. This process is called unpivoting.

There is also an opposite operation called pivoting, which transforms rows into columns. We can use unpivoting within a single table if we want to display multiple columns as rows. It is not always necessary to use multiple tables for this.

Here are more details:

• PIVOT: The PIVOT operator in SQL is used to transform rows into columns. It is useful when summarizing or restructuring data in a cross-tabular format.
• UNPIVOT: The UNPIVOT operator performs the opposite function of PIVOT, transforming columns back into rows.

---

Example using PIVOT and UNPIVOT on the Same Scenario

Scenario Recap

• We have two tables:
  1. Customers Table (Customers) → Contains CustomerID, Name, and PhoneNumber.
  2. Orders Table (Orders) → Contains OrderID, CustomerName, and five Contact columns.
• Goal: Use PIVOT to restructure data, then use UNPIVOT to revert it back.

---

1️⃣ PIVOT Example (Convert Phone Numbers from Rows to Columns)

We will pivot the Orders table to show customer contacts in a different format.

SQL Query Using PIVOT

SELECT CustomerName, [Contact1], [Contact2], [Contact3], [Contact4], [Contact5]
FROM (
    SELECT CustomerName, ContactType, ContactNumber
    FROM Orders
    UNPIVOT (
        ContactNumber FOR ContactType IN (Contact1, Contact2, Contact3, Contact4, Contact5)
    ) AS UnpivotedOrders
) AS SourceTable
PIVOT (
    MAX(ContactNumber) 
    FOR ContactType IN ([Contact1], [Contact2], [Contact3], [Contact4], [Contact5])
) AS PivotedTable;

---

Expected Output:

CustomerName	Contact1	Contact2	Contact3	Contact4	Contact5
Alice	1234567890	NULL	NULL	NULL	NULL
Bob	NULL	9876543210	NULL	NULL	NULL
Charlie	NULL	NULL	5556667777	NULL	NULL
David	NULL	NULL	NULL	2223334444	NULL

---

2️⃣ UNPIVOT Example (Convert Columns Back into Rows)

We will now unpivot the five Contact columns back into rows.

SQL Query Using UNPIVOT

SELECT CustomerName, ContactType, ContactNumber
FROM Orders
UNPIVOT (
    ContactNumber FOR ContactType IN (Contact1, Contact2, Contact3, Contact4, Contact5)
) AS UnpivotedOrders
WHERE ContactNumber IS NOT NULL;

---

Expected Output (Reverting to Row Format):

CustomerName	ContactType	ContactNumber
Alice	Contact1	1234567890
Bob	Contact2	9876543210
Charlie	Contact3	5556667777
David	Contact4	2223334444