8.3 Pivoting and Unpivoting

 Pivoting and unpivoting are techniques used to transform data in SQL Server. Pivoting involves converting rows into columns, and unpivoting involves converting columns into rows.

8.3.1 Pivoting Data

Example Scenario:

Consider a table named Sales with the following structure:

CREATE TABLE Sales (
    ProductName VARCHAR(50),
    SalesYear INT,
    Revenue DECIMAL(10, 2)
);
INSERT INTO Sales VALUES
('Product_A', 2021, 1000.00),
('Product_A', 2022, 1200.00),
('Product_B', 2021, 800.00),
('Product_B', 2022, 900.00);
```

Pivoting Data Using PIVOT:

SELECT *

FROM (

    SELECT ProductName, SalesYear, Revenue

    FROM Sales

) AS Source

PIVOT (

    SUM(Revenue)

    FOR SalesYear IN ([2021], [2022])

) AS PivotTable;

Output:

ProductName	2021	2022
Product_A	1000.00	1200.00
Product_B	800.00	900.00

8.3.2 Unpivoting Data

Example Scenario:

Consider a table named MonthlySales with the following structure:

CREATE TABLE MonthlySales (
    ProductName VARCHAR(50),
    January DECIMAL(10, 2),
    February DECIMAL(10, 2),
    March DECIMAL(10, 2)
);
INSERT INTO MonthlySales VALUES
('Product_X', 500.00, 600.00, 700.00),
('Product_Y', 300.00, 400.00, 500.00);

Unpivoting Data Using UNPIVOT:

SELECT ProductName, [Month], SalesAmount
FROM (
    SELECT ProductName, January, February, March
    FROM MonthlySales
) AS Source
UNPIVOT (
    SalesAmount FOR [Month] IN (January, February, March)
) AS UnpivotTable;

Output:

ProductName	Month	SalesAmount
Product_X	January	500.00
Product_X	February	600.00
Product_X	March	700.00
Product_Y	January	300.00
Product_Y	February	400.00
Product_Y	March	500.00

In this tutorial, we covered the basics of pivoting and unpivoting in SQL Server, providing practical examples and presenting the query outputs in a table format. Feel free to adapt the examples to your specific use case and explore more advanced scenarios.