PIVOT Table
PIVOT and UNPIVOT are relational operators that are used to transform a table-valued expression into another table. PIVOT rotates a table valued expression by turning the unique values from one column in to multiple columns in the output, and aggregate function is performed where they are needed on any remaining column values that are required in output. In simple words, it simply turns the value of a specified column into column names, effectively rotating a table. The reverse operation of PIVOT is UNPIVOT. UNPIVOT operator rotates the column of table valued expression into column names.
Syntax
select <non-pivoted column>,
[first pivoted column] as <column name>,
[second pivoted column name] as <column name>,
. . . . . .
[last pivoted column] as <column name>
from
(select query that produces the data)
as TableAlias
PIVOT
(
<aggregation function>(column being aggregated)--MIN,MAX,SUM,etc
FOR
[<column that contains the values that will become column headers>]
IN ([first pivoted column], [second pivoted column].....[last pivoted column] )
AS PivotTableAlias
<optional ORDER BY clause>
Example
Creation of Table
create table ProductSales(CustomerName varchar(15), ProductName varchar(15),QuantityNeeded int)
Insertion of Data and Select table
insert into ProductSales
select 'Tim', 'Coke',5 union all
select 'Tim', 'Bread',3 union all
select 'John','Sandwich',4 union all
select 'Tim','Milk',2 union all
select'John','Bread',3 union all
select 'John','Coke',6 union all
select 'Mike','Soda',12 union all
select 'Mike', 'Coke',5 union all
select 'Mike', 'Bread',1 union all
select 'Mike','Sandwich',2 union all
select 'Tim','Milk',6
select * from ProductSales
Output:
PIVOT Table
--Pivot Table ordered by PRODUCTNAME
SELECT ProductName, John, Mike
FROM (
SELECT CustomerName, ProductName, QuantityNeeded
FROM ProductSales) up
PIVOT (SUM(QuantityNeeded) FOR CustomerName IN (John, Mike)) AS [pivot]
ORDER BY ProductName
GO
Output:
Suppose you want to determine number of products needed by certain customers. The following query provides this report ordered by customer.