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.

Pivoting in SQL

 Pivoting in SQL involves transforming row-level data into columnar data. This is particularly useful when you want to rotate data from multiple rows into a single row with multiple columns. Here's an example to illustrate pivoting in SQL:

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

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

You want to pivot this data to show the total sales amount for each product by customer, like this:

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

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

sql
SELECT 
    CustomerID,
    [Product A],
    [Product B]
FROM 
    (SELECT CustomerID, Product, Amount FROM Sales) AS SourceTable
PIVOT
    (
    SUM(Amount)
    FOR Product IN ([Product A], [Product B])
    ) AS PivotTable;

Explanation:

• The inner query (SELECT CustomerID, Product, Amount FROM Sales) AS SourceTable selects the necessary columns from the Sales table.
• The PIVOT keyword is used to pivot the data.
• SUM(Amount) aggregates the amounts for each product and customer combination.
• FOR Product IN ([Product A], [Product B]) specifies the columns that you want to pivot.
• AS PivotTable is an alias for the pivoted result set.

The result will be the pivoted table with CustomerID, Product A, and Product B columns showing the total sales amount for each product by customer.