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