FROM - Using PIVOT and UNPIVOT
You can use the
UNPIVOT relational operators to change a table-valued expression into another table.
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and runs aggregations where they're required on any left over column values that are wanted in the final output.
UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
The syntax for
PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of
SELECT...CASE statements. For a complete description of the syntax for
PIVOT, see FROM (Transact-SQL).
The following syntax summarizes how to use the
SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name> FROM (<SELECT query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>;
The column identifiers in the
UNPIVOT clause follow the catalog collation. For SQL Database, the collation is always
SQL_Latin1_General_CP1_CI_AS. For SQL Server partially contained databases, the collation is always
Latin1_General_100_CI_AS_KS_WS_SC. If the column is combined with other columns, then a collate clause (
COLLATE DATABASE_DEFAULT) is required to avoid conflicts.
Basic PIVOT Example
The following code example produces a two-column table that has four rows.
USE AdventureWorks2014 ; GO SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture;
Here is the result set.
DaysToManufacture AverageCost ----------------- ----------- 0 5.0885 1 223.88 2 359.1082 4 949.4105
No products are defined with three
The following code displays the same result, pivoted so that the
DaysToManufacture values become the column headings. A column is provided for three
 days, even though the results are
-- Pivot table with one row and five columns SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, , , , ,  FROM (SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable PIVOT ( AVG(StandardCost) FOR DaysToManufacture IN (, , , , ) ) AS PivotTable;
Here is the result set.
Cost_Sorted_By_Production_Days 0 1 2 3 4 ------------------------------ ----------- ----------- ----------- ----------- ----------- AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Complex PIVOT Example
A common scenario where
PIVOT can be useful is when you want to generate cross-tabulation reports to give a summary of the data. For example, suppose you want to query the
PurchaseOrderHeader table in the
AdventureWorks2014 sample database to determine the number of purchase orders placed by certain employees. The following query provides this report, ordered by vendor.
USE AdventureWorks2014; GO SELECT VendorID,  AS Emp1,  AS Emp2,  AS Emp3,  AS Emp4,  AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( , , , ,  ) ) AS pvt ORDER BY pvt.VendorID;
Here is a partial result set.
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
The results returned by this subselect statement are pivoted on the
SELECT PurchaseOrderID, EmployeeID, VendorID FROM PurchaseOrderHeader;
The unique values returned by the
EmployeeID column become fields in the final result set. As such, there's a column for each
EmployeeID number specified in the pivot clause: in this case employees
PurchaseOrderID column serves as the value column, against which the columns returned in the final output, which are called the grouping columns, are grouped. In this case, the grouping columns are aggregated by the
COUNT function. Notice that a warning message appears that indicates that any null values appearing in the
PurchaseOrderID column weren't considered when computing the
COUNT for each employee.
When aggregate functions are used with
PIVOT, the presence of any null values in the value column are not considered when computing an aggregation.
UNPIVOT carries out almost the reverse operation of
PIVOT, by rotating columns into rows. Suppose the table produced in the previous example is stored in the database as
pvt, and you want to rotate the column identifiers
Emp5 into row values that correspond to a particular vendor. As such, you must identify two additional columns. The column that will contain the column values that you're rotating (
Emp2,...) will be called
Employee, and the column that will hold the values that currently exist under the columns being rotated will be called
Orders. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition. Here is the query.
-- Create the table and insert values as portrayed in the previous example. 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 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
Here is a partial result set.
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 ...
UNPIVOT isn't the exact reverse of
PIVOT carries out an aggregation and merges possible multiple rows into a single row in the output.
UNPIVOT doesn't reproduce the original table-valued expression result because rows have been merged. Also, null values in the input of
UNPIVOT disappear in the output. When the values disappear, it shows that there may have been original null values in the input before the
Sales.vSalesPersonSalesByFiscalYears view in the AdventureWorks2012 sample database uses
PIVOT to return the total sales for each salesperson, for each fiscal year. To script the view in SQL Server Management Studio, in Object Explorer, locate the view under the Views folder for the AdventureWorks2012 database. Right-click the view name, and then select Script View as.
Send feedback about: