Pivot Table in SQL Server: Transforming Rows into Columns for Enhanced Data Analysis

The PIVOT and UNPIVOT relational operators in SQL Server are powerful tools for reshaping data, allowing you to transform table-valued expressions into new table formats. Specifically, PIVOT is used to rotate a table by converting unique values from a column into distinct columns in the output. This process often involves aggregations on any remaining column values, providing a summarized and restructured view of your data. Conversely, UNPIVOT performs the reverse operation, transforming columns back into row values.

Understanding and utilizing PIVOT and UNPIVOT can significantly simplify complex data analysis and reporting tasks, often replacing verbose and less readable SELECT...CASE statement series. This article will delve into the syntax, usage, and practical applications of these operators, using examples based on the AdventureWorks2022 sample database.

It’s worth noting that while PIVOT and UNPIVOT are incredibly useful, excessive use within a single Transact-SQL statement can potentially impact query performance. It’s always recommended to test and optimize your queries for efficiency.

The examples provided in this guide are based on the AdventureWorks2022 or AdventureWorksDW2022 sample databases, readily available for download from the Microsoft SQL Server Samples and Community Projects page.

Syntax Overview

Let’s briefly review the syntax for both PIVOT and UNPIVOT operators.

PIVOT Syntax:

SELECT [ <non-pivoted column> [ AS <column_name> ] , ] ...
       [ <first pivoted column> [ AS <column_name> ] ,
         [ <second pivoted column> [ AS <column_name> ] , ] ...
         [ <last pivoted column> [ AS <column_name> ] ] ]
FROM   ( <table-valued expression> ) AS <source_table>
PIVOT  ( <aggregation_function>(<value_column>)
         FOR <pivot_column>
         IN ( [ <first_pivot_column> ], [ <second_pivot_column> ],
              ... [ <last_pivot_column> ] ) ) AS <pivot_table>;

UNPIVOT Syntax:

SELECT [ <non-pivoted column> [ AS <column_name> ] , ] ...
       <output_column> [ AS <column_name> ] ,
       <value_column> [ AS <column_name> ]
FROM   ( <table-valued expression> ) AS <source_table>
UNPIVOT
       ( <value_column>
         FOR <pivot_column>
         IN ( <first_column>, <second_column>,
              ... <last_column> ) ) AS <unpivot_table>;

Important Considerations

When working with UNPIVOT, it’s crucial to understand that column identifiers adhere to the catalog collation.

• In Azure SQL Database, the collation is fixed at SQL_Latin1_General_CP1_CI_AS.
• For SQL Server partially contained databases, the collation is Latin1_General_100_CI_AS_KS_WS_SC.

If you’re combining columns with different collations, you might need to use the COLLATE DATABASE_DEFAULT clause to prevent collation conflicts.

Furthermore, users of Microsoft Fabric and Azure Synapse Analytics pools should be aware of a limitation: queries using the PIVOT operator will fail if a GROUP BY clause is applied to the non-pivot column output by PIVOT. A simple workaround is to remove the redundant GROUP BY clause, as it often doesn’t alter the query results in these scenarios.

Basic PIVOT Operation: Transforming Days to Manufacture into Columns

Let’s start with a fundamental PIVOT example. Consider a scenario where you want to analyze the average standard cost of products based on their manufacturing lead time, represented by DaysToManufacture.

First, let’s execute a simple GROUP BY query to understand the initial data structure:

USE AdventureWorks2022;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;

This query yields a two-column result set, showing the average cost for each distinct DaysToManufacture value.

DaysToManufacture AverageCost
------------------ ------------
0                  5.0885
1                  223.88
2                  359.1082
4                  949.4105

Notably, there are no products with a DaysToManufacture value of 3.

Now, let’s use PIVOT to transform the DaysToManufacture values into column headers. We’ll explicitly include a column for ‘3’ days, even though no data exists for it, demonstrating how PIVOT handles missing values.

-- Pivot table to display average cost sorted by production days
SELECT 'AverageCost' AS CostSortedByProductionDays,
       [0], [1], [2], [3], [4]
FROM   (SELECT DaysToManufacture, StandardCost
        FROM Production.Product) AS SourceTable
PIVOT  (AVG(StandardCost)
       FOR DaysToManufacture IN ([0], [1], [2], [3], [4])) AS PivotTable;

This PIVOT query restructures the data, presenting DaysToManufacture (0, 1, 2, 3, 4) as columns and the corresponding average StandardCost as values under the “AverageCost” row.

CostSortedByProductionDays 0          1          2          3     4
--------------------------- ---------- ---------- ---------- ----- ----------
AverageCost               5.0885     223.88     359.1082   NULL  949.4105

The result set of a basic PIVOT example, showcasing DaysToManufacture as columns and AverageCost as values.

This basic example illustrates the core functionality of PIVOT: rotating column values into column headers and performing aggregations.

Advanced PIVOT for Cross-Tabulation Reports

PIVOT becomes particularly valuable when generating cross-tabulation reports, which summarize data across multiple categories. Consider the scenario of analyzing purchase order data in the AdventureWorks2022 database. Suppose you need a report detailing the number of purchase orders placed by specific employees, categorized by vendor.

The following query leverages PIVOT to create such a report, ordered by VendorID.

USE AdventureWorks2022;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM (SELECT PurchaseOrderID, EmployeeID, VendorID
      FROM Purchasing.PurchaseOrderHeader) p
PIVOT  (COUNT(PurchaseOrderID)
       FOR EmployeeID IN ([250], [251], [256], [257], [260])) AS pvt
ORDER BY pvt.VendorID;

In this complex PIVOT example:

1. The subquery SELECT PurchaseOrderID, EmployeeID, VendorID FROM PurchaseOrderHeader retrieves the necessary data.
2. PIVOT operator transforms EmployeeID values (250, 251, 256, 257, 260) into column headers (Emp1, Emp2, Emp3, Emp4, Emp5).
3. COUNT(PurchaseOrderID) aggregates the number of purchase orders for each employee and vendor combination.
4. The result is ordered by VendorID.

A portion of the resulting dataset is shown below:

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
----------- ----------- ----------- ----------- ----------- -----------
1492        2           5           4           4           4
1494        2           5           4           5           4
1496        2           4           4           5           5
1498        2           5           4           4           4
1500        3           4           4           5           4
...

A partial result set from a complex PIVOT query, showing purchase order counts by VendorID and EmployeeID.

This output clearly presents a cross-tabulation of purchase order counts, making it easy to compare employee activity across different vendors. It’s important to remember that when using aggregate functions with PIVOT, any NULL values in the value_column (in this case, PurchaseOrderID) are ignored during aggregation.

Reversing PIVOT with UNPIVOT

UNPIVOT is designed to reverse the PIVOT operation, converting columns back into rows. Imagine you have the pivoted table pvt from the previous example and want to revert the Employee columns (Emp1, Emp2, Emp3, Emp4, Emp5) back into row values, associating each with its corresponding VendorID and order count.

To achieve this, you would use UNPIVOT, specifying the columns to unpivot (Emp1, Emp2, etc.) and defining new columns for the employee identifier and the order count.

-- Create sample pivoted table 'pvt' (assuming it's already created as in the original article)
CREATE TABLE pvt (
    VendorID INT,
    Emp1 INT, Emp2 INT, Emp3 INT, Emp4 INT, Emp5 INT
);
GO
INSERT INTO pvt VALUES (1, 4, 3, 5, 4, 4);
INSERT INTO pvt VALUES (2, 4, 1, 5, 5, 5);
INSERT INTO pvt VALUES (3, 4, 3, 5, 4, 4);
INSERT INTO pvt VALUES (4, 4, 2, 5, 5, 4);
INSERT INTO pvt VALUES (5, 5, 1, 5, 5, 5);
GO

-- Unpivot the 'pvt' table
SELECT VendorID, Employee, Orders
FROM   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p
UNPIVOT
       (Orders
        FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)) AS unpvt;
GO

In this UNPIVOT query:

1. We select from a subquery that retrieves the VendorID and the employee columns (Emp1 to Emp5) from the pvt table.
2. UNPIVOT operator transforms the specified columns (Emp1, Emp2, etc.) into rows.
3. Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) defines:
   • Orders as the new column to hold the values from the unpivoted columns.
   • Employee as the new column to hold the names of the original columns (Emp1, Emp2, etc.).

The UNPIVOT operation results in the following output (partial):

VendorID    Employee    Orders
----------- ----------- ------
1           Emp1        4
1           Emp2        3
1           Emp3        5
1           Emp4        4
1           Emp5        4
2           Emp1        4
2           Emp2        1
2           Emp3        5
2           Emp4        5
2           Emp5        5
...

A partial result set after UNPIVOT operation, showing columns transformed back into rows with VendorID, Employee, and Orders.

It’s important to note that UNPIVOT isn’t a perfect reversal of PIVOT. PIVOT performs aggregations, potentially merging multiple rows into one. UNPIVOT cannot reconstruct the original table-valued expression perfectly, especially if aggregations were involved in the PIVOT operation. Furthermore, NULL values present before the PIVOT operation will not be present in the output of UNPIVOT.

Real-World Application: Sales Data Analysis

For a practical example of PIVOT in a real-world scenario, consider the Sales.vSalesPersonSalesByFiscalYears view in the AdventureWorks2022 database. This view utilizes PIVOT to present the total sales for each salesperson across different fiscal years. You can examine the underlying query of this view in SQL Server Management Studio by right-clicking on the view in Object Explorer and selecting Script View as -> CREATE To -> New Query Editor Window. This will reveal the PIVOT logic used in a pre-built view for sales analysis.

Conclusion

PIVOT and UNPIVOT are invaluable relational operators in SQL Server for data transformation and analysis. PIVOT simplifies the creation of cross-tabulation reports by rotating column values into headers, while UNPIVOT reverses this process, converting columns back into rows. Mastering these operators enhances your ability to reshape data for insightful reporting and analysis within SQL Server environments. By understanding their syntax and practical applications, you can write more efficient and readable SQL queries for complex data manipulation tasks.

Related Content

(Links to related SQL Server documentation or rental-server.net resources could be added here if available and relevant)