In this article, we will explore the SQL Pivot and SQL Unpivot operators and how they can be used to transpose data in SQL Server. These operators are useful when we need to transform a table-valued expression from rows to columns or from columns to rows.

What is SQL Pivot?

SQL Pivot is a technique that allows us to transpose rows into columns and perform aggregations along the way. It transposes a table-valued expression from a unique set of values from one column into multiple columns in the output. This is particularly useful when we want to summarize data and present it in a more readable format.

What is SQL Unpivot?

SQL Unpivot performs the opposite operation of SQL Pivot. It transforms the columns of a table-valued expression into column values. This is useful when we want to convert columns into rows and analyze the data in a different way.

Static vs Dynamic Pivoting

In most cases, the static pivoting technique is sufficient for business requirements. For example, when we need to create a monthly sales forecast or aggregate sales data by year or quarter. In these cases, the columns in the IN clause of the PIVOT operator remain static.

However, there are instances where we need more flexibility and the table-valued expression is more dynamic in nature. In these cases, we can use dynamic PIVOT to handle new sets of expressions that are included in the table. This allows us to adapt to changing data without modifying the query.

Example: Performing a SQL Pivot Operation

Let’s consider a sample dataset derived from the AdventureWorks2014 database. We have a simple dataset with a SalesYear column and a TotalSales column. If we want to transpose this data, we can use the PIVOT operator to create columns for each distinct year and display the corresponding sales values.

SELECT *
FROM (
    SELECT YEAR(SOH.OrderDate) AS SalesYear, SOH.SubTotal AS TotalSales
    FROM sales.SalesOrderHeader SOH
    JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) AS Sales
PIVOT (
    SUM(TotalSales)
    FOR SalesYear IN ([2011], [2012], [2013], [2014])
) AS PVT

This query will generate a pivoted table with the distinct years as columns and the corresponding sales values. This makes it easier to analyze and compare sales data across different years.

Example: Performing a SQL Unpivot Operation

Now, let’s look at the opposite operation – un-pivoting. This is as simple as going from horizontal to vertical. We take the distinct columns selected in the pivot operation and turn them into their own rows.

SELECT SalesYear, TotalSales
FROM (
    SELECT *
    FROM (
        SELECT YEAR(SOH.OrderDate) AS SalesYear, SOH.SubTotal AS TotalSales
        FROM sales.SalesOrderHeader SOH
        JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
    ) AS Sales
    PIVOT (
        SUM(TotalSales)
        FOR SalesYear IN ([2011], [2012], [2013], [2014])
    ) AS PVT
) AS T
UNPIVOT (
    TotalSales
    FOR SalesYear IN ([2011], [2012], [2013], [2014])
) AS upvt;

This query will un-pivot the pivoted dataset and display the sales data in a vertical format. It allows us to analyze the data in a different way and perform further calculations or aggregations.

Conclusion

In this article, we have explored the SQL Pivot and SQL Unpivot operators and how they can be used to transpose data in SQL Server. We have seen examples of both static and dynamic pivoting techniques and how they can be applied to different scenarios. By understanding these operators, we can manipulate and analyze data more effectively in SQL Server.

If you have any questions or comments, please feel free to leave them below.