Unpivoting in SQL

 Unpivoting in SQL involves converting column-wise data into row-wise data. This is useful when you want to rotate data from multiple columns into a single column. Here's an example to illustrate unpivoting in SQL:

Let's say you have a table Sales with the following structure:

sql
| CustomerID | Product A | Product B |
|------------|-----------|-----------|
| 1          | 100       | 200       |
| 2          | 150       | NULL      |
| 3          | NULL      | 300       |

You want to unpivot this data to show each product's sales amount along with the respective customer ID, like this:

mathematica
| CustomerID | Product   | Amount |
|------------|-----------|--------|
| 1          | Product A | 100    |
| 1          | Product B | 200    |
| 2          | Product A | 150    |
| 3          | Product B | 300    |

You can achieve this using SQL's UNPIVOT syntax. Here's how you can do it:

sql
SELECT 
    CustomerID,
    Product,
    Amount
FROM 
    (SELECT CustomerID, ProductA, ProductB FROM Sales) AS SourceTable
UNPIVOT
    (
    Amount FOR Product IN (ProductA, ProductB)
    ) AS UnpivotTable;

Explanation:

• The inner query (SELECT CustomerID, ProductA, ProductB FROM Sales) AS SourceTable selects the necessary columns from the Sales table.
• The UNPIVOT keyword is used to unpivot the data.
• Amount FOR Product IN (ProductA, ProductB) specifies the columns that you want to unpivot.
• AS UnpivotTable is an alias for the unpivoted result set.

The result will be the unpivoted table with CustomerID, Product, and Amount columns showing each product's sales amount along with the respective customer ID.