PIVOT
PIVOT operator rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
Syntax
pivot_clause : table_reference
PIVOT LEFT_PAREN aggregate_function ( AS alias )? (COMMA aggregate_function ( AS alias )? )*
pivot_for_clause
pivot_in_clause
RIGHT_PAREN
pivot_for_clause : FOR ( column
| LEFT_PAREN column ( COMMA column )* RIGHT_PAREN
)
pivot_in_clause : IN LEFT_PAREN ( expression ( AS identifier )? ( COMMA expression ( AS identifier )? )*
| pivot_multiple_columns ( COMMA pivot_multiple_columns )*
| subquery
| ANY
)
RIGHT_PAREN
pivot_multiple_columns : LEFT_PAREN expression ( COMMA expression )* RIGHT_PAREN
( AS identifier )?
Remarks
• The pivot operator will take the left side table_reference's projections as inputs. The argument to the aggregate_function must be a projection from the table_reference.
• The column specified in the pivot_for_clause clause must be a projection from table_reference. And will be matched against the expressions in the IN clause.
• All other projections in the table_referenced will be GROUP'ed BY.
Example
SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp4
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(COUNT (PurchaseOrderID)
FOR EmployeeID IN
( 250 as Emp1, 251 as Emp2, 256 as Emp3, 257 as Emp4, 260 as Emp5 )
) AS pvt
The PIVOT operator essentially invokes the following SQL
select VendorID, COUNT (PurchaseOrderID), EmployeeID
FROM Purchasing.PurchaseOrderHeader
WHERE EmployeeID IN 250, 251, 256, 257, 260)
GROUP BY VendorID, EmployeeID
An example result set of the above SQL is:
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( 250 as Emp1, 251 as Emp2, 256 as Emp3, 257 as Emp4, 260 as Emp5)
)
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