SQL SERVER – Exploring PIVOT and UNPIVOT

The SQL Server PIVOT and UNPIVOT operators are powerful tools that provide an easy way to transform your data in SQL. PIVOT allows you to rotate rows into columns, effectively creating a cross-tabulation of your data, whereas UNPIVOT performs the opposite operation, turning columns into rows.

In this blog post, we’ll walk through examples of using both PIVOT and UNPIVOT with SQL Server, starting by creating sample data.

1. Creating Sample Data

To get started, let’s create a simple table named ‘Sales’ with the following structure and data:

CREATE TABLE Sales
(
    SalesPerson VARCHAR(50),
    Year INT,
    SalesAmount DECIMAL(10, 2)
)
INSERT INTO Sales (SalesPerson, Year, SalesAmount)
VALUES 
('John', 2021, 50000.00),
('John', 2022, 55000.00),
('Jane', 2021, 45000.00),
('Jane', 2022, 60000.00),
('James', 2021, 70000.00),
('James', 2022, 75000.00)

Our ‘Sales’ table now has the following data:

2. PIVOT

PIVOT allows us to rotate rows into columns, effectively creating a cross-tabulation of data. Here’s how we can use PIVOT to display each person’s sales by year:

SELECT SalesPerson, [2021], [2022]
FROM 
(
    SELECT SalesPerson, Year, SalesAmount
    FROM Sales
) as SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Year IN ([2021], [2022])
) as PivotTable

The output will be:

In the PIVOT statement, we specify the aggregate function we want to apply (in this case, SUM), the column we want to pivot (Year), and the values we want to include in the pivot (2021, 2022).

3. UNPIVOT

UNPIVOT performs the opposite operation to PIVOT, turning columns into rows. Let’s take the pivoted table and unpivot it:

SELECT SalesPerson, Year, SalesAmount
FROM
(
    SELECT SalesPerson, [2021], [2022]
    FROM Sales
    PIVOT
    (
        SUM(SalesAmount)
        FOR Year IN ([2021], [2022])
    ) AS PivotTable
) as SourceTable
UNPIVOT
(
    SalesAmount
    FOR Year IN ([2021], [2022])
) as UnpivotTable

The output will be:

The UNPIVOT statement specifies the column we want to unpivot (SalesAmount) and the columns we’d like to include in the unpivot operation (2021, 2022).

Conclusion

The SQL Server PIVOT and UNPIVOT operators are pretty versatile and provide an easy way to transform your data in SQL. Remember, though, that while these operators can be beneficial, they can also be challenging to use effectively due to the need to specify all column values in advance. As always, remember to test your queries thoroughly to ensure they’re returning the results you expect!

The key takeaway from this post is to remember that PIVOT and UNPIVOT are just tools in your SQL toolbelt and should be used when appropriate. Exploring SQL Server PIVOT and UNPIVOT.

You can follow me on X (twitter).

Reference: Pinal Dave (https://blog.sqlauthority.com)