The database users might need to view data in a user-defined format. These reports might involve summarizing data on the basis of various criteria. SQL Server allows you to generate summarized data reports using the PIVOT clause of SELECT statement.
The PIVOT operator is used to transform a set of columns into values. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. In addition, it also performs aggregations on the remaining column values if required in the output. Following is the syntax of PIVOT operator is:
SELECT * from table_name
PIVOT (aggregation_function (value_column)
FOR pivot_column
IN (column_list)
) table_alias
Where,
Consider an example, you want to display the number of purchase orders placed by certain employees, laid down with the vendors. The following query provides this report:
SELECT VendorID, [164] AS Empl, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198],[223], [231], [233] )
) AS pvt
ORDER BY VendorID
Following output is displayed by the preceding statements.
The PIVOT operator is used to transform a set of columns into values. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. In addition, it also performs aggregations on the remaining column values if required in the output. Following is the syntax of PIVOT operator is:
SELECT * from table_name
PIVOT (aggregation_function (value_column)
FOR pivot_column
IN (column_list)
) table_alias
Where,
- Table_name: name of table on which query will execute.
- Pivot_column: the only column on which condition perform.
- Table_alias: alias name of the table used in query only.
Consider an example, you want to display the number of purchase orders placed by certain employees, laid down with the vendors. The following query provides this report:
SELECT VendorID, [164] AS Empl, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198],[223], [231], [233] )
) AS pvt
ORDER BY VendorID
Following output is displayed by the preceding statements.
