Can you explain the use of the PIVOT and UNPIVOT operators in SQL Server? — SQL Server Interview Question #34

PIVOT:
The PIVOT operator is used to rotate rows into columns resulting in a summary table. This operator is useful in situations where the data needs to be transformed from a row-based format to a column-based format.

Example:

Consider a table called sales which has the following data:

| Product | Month | Sales |
|---------|-------|-------|
| Prod_1  | Jan   | 100   |
| Prod_1  | Feb   | 200   |
| Prod_1  | Mar   | 300   |
| Prod_2  | Jan   | 150   |
| Prod_2  | Feb   | 250   |
| Prod_2  | Mar   | 350   |

To represent this data in a column-based format with the products as the columns and the months as the rows, we can use the PIVOT operator as shown below:

SELECT Month, Prod_1, Prod_2 
FROM
(
  SELECT Product, Month, Sales
  FROM sales
) src
PIVOT
(
  SUM(Sales)
  FOR Product IN (Prod_1, Prod_2)
) piv;

This will result in the following output:

| Month | Prod_1 | Prod_2 |
|-------|--------|--------|
| Jan   | 100    | 150    |
| Feb   | 200    | 250    |
| Mar   | 300    | 350    |

UNPIVOT:
The UNPIVOT operator is used to rotate columns into rows resulting in a normalized table. This operator is useful in situations where we need to transform column-based data to row-based data.

Example:

Consider a table called sales which has the following data:


| Month | Prod_1 | Prod_2 |
|-------|--------|--------|
| Jan   | 100    | 150    |
| Feb   | 200    | 250    |
| Mar   | 300    | 350    |

To represent this data in a row-based format with each row representing a product and a month, we can use the UNPIVOT operator as shown below:

SELECT Month, Product, Sales
FROM
(
  SELECT Month, Prod_1, Prod_2
  FROM sales
) src
UNPIVOT
(
  Sales FOR Product IN (Prod_1, Prod_2)
) unpiv;

This will result in the following output:

| Month | Product | Sales |
|-------|---------|-------|
| Jan   | Prod_1  | 100   |
| Jan   | Prod_2  | 150   |
| Feb   | Prod_1  | 200   |
| Feb   | Prod_2  | 250   |
| Mar   | Prod_1  | 300   |
| Mar   | Prod_2  | 350   |

In summary, PIVOT and UNPIVOT are powerful operators in SQL Server that allow us to transform data from row-based or column-based formats. These operators are especially useful in situations where we need to summarize or normalize data.