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.