A PIVOT 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.
Example 1
Table One
Id Area Result
1 A Pass
2 B Fail
3 A Pass
4 B Pass
5 A Fail
SELECT * FROM dbo.Result
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Output
Area Pass Fail
A 2 1
B 1 1
Note: the "select * from result" will contain id, area and result columns, but it will display only an area because the id and result columns are used in a pivot, in other words count (id) and for result. So the select statement will display area, pass and fail columns.
SELECT * FROM dbo.Result
PIVOT ( count(Id) FOR Result in (Pass, Fail, Promoted) ) AS RESULT
Output
Area Pass Fail Promoted
A 2 1 0
B 1 1 0
SELECT * FROM dbo.Result
PIVOT ( count(Area) FOR Result in (Pass, Fail) ) AS RESULT
Output
Id Pass Fail
1 1 0
2 0 1
3 1 0
4 1 0
5 0 1
Example 2
Sales Table
Month SaleAmount
January 100
February 200
March 300
SELECT * FROM SALES
PIVOT ( SUM(SaleAmount) FOR Month IN (January, February, March)) AS A
OR
SELECT [January]
, [February]
, [March]
FROM ( SELECT [Month]
, SaleAmount
FROM Sales
) p PIVOT ( SUM(SaleAmount)
FOR [Month]
IN ([January],[February],[March])
) AS pvt
Output
January February March
100 200 300
SELECT * FROM SALES
PIVOT ( COUNT(SaleAmount) FOR Month IN (January, February, March)) AS A
Output
January February March
1 1 1
SELECT * FROM SALES
PIVOT ( count(Month) FOR Month IN (January, February, March)) AS A
Output
SaleAmount January February March
100 1 0 0
200 0 1 0
300 0 0 1
Example 3
T1 Table
No ID Date Value
1 1001 2009-05-01 00:00:00.000 101.00
1 1001 2009-05-15 00:00:00.000 102.00
1 1001 2009-05-20 00:00:00.000 105.00
2 1001 2009-05-01 00:00:00.000 41.00
2 1001 2009-05-15 00:00:00.000 44.00
3 1001 2009-06-01 00:00:00.000 330.00
SELECT * FROM T1
PIVOT ( SUM(Value) FOR Date in ([05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]) ) as a
OR
SELECT [No] , [ID] ,[05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]
FROM (
SELECT [No], [ID], [Date], [Value]
FROM T1) up
PIVOT ( sum([Value]) FOR [Date] in ([05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]) )AS pvt
Output
No ID 05/01/2009 05/15/2009 05/20/2009 06/01/2009
1 1001 101.00 102.00 105.00 NULL
2 1001 41.00 44.00 NULL NULL
3 1001 NULL NULL NULL 330.00
Example 4: WHY 2 SELECT STATEMENTS IN PIVOT?
Consider Example 1.
Result Table
Id Area Result
1 A Pass
2 B Fail
3 A Pass
4 B Pass
5 A Fail
SELECT * FROM dbo.Result
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Or
SELECT * FROM
( SELECT * FROM dbo.Result )
AS P
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Output
Area Pass Fail
A 2 1
B 1 1
The second statement has two select statements. Why do we need two select statements?
SELECT * FROM dbo.Result
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Works.
SELECT * FROM dbo.Result
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Error in the following select statement.
SELECT * FROM dbo.Result
where Area = 'A' -- ERROR
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
So we need to combine the results like this:
SELECT * FROM
(SELECT * FROM dbo.Result WHERE Area = 'A') as P
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Output
Area Pass Fail
A 2 1
Notes
The following produces an error:
SELECT * FROM
(SELECT Pass, * FROM dbo.Result WHERE Area = 'A') as P -- ERROR AS Pass IS NOT AVABLE
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
The following works:
SELECT * FROM
(SELECT Id, Area, Result FROM dbo.Result WHERE Area = 'A') as P
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
The following produces an error:
SELECT Id, Area, Result, Pass, Fail FROM -- Error as Id and Result columns are used in pivot
(SELECT Id, Area, Result FROM dbo.Result WHERE Area = 'A') as P
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
The following works:
SELECT Area, Pass, Fail FROM
(SELECT Id, Area, Result FROM dbo.Result WHERE Area = 'A') as P
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Or:
SELECT Area, Pass, Fail FROM
(SELECT * FROM dbo.Result WHERE Area = 'A') as P
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
The following works:
SELECT Area AS 'AREA_NAME', Pass AS 'PASS_COUNT', Fail AS 'FAIL_COUNT' -- Columns names are changes
FROM
(SELECT * FROM dbo.Result WHERE Area = 'A') as P
PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Output
AREA_NAME PASS_COUNT FAIL_COUNT
A 2 1
Example 5
TABLE
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
PIVOT
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(
SELECT DaysToManufacture, StandardCost
FROM Production.Product
) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Example 6
TABLE
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;
PIVOT
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
Output
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
Example 7
TABLE
CREATE TABLE invoice (
InvoiceNumber VARCHAR(20),
invoiceDate DATETIME,
InvoiceAmount MONEY
)
PIVOT
SELECT *
FROM (
SELECT
year(invoiceDate) as [year],
left(datename(month,invoicedate),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount) FOR [month] IN (
jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
)
)AS p
EXAMPLE 8
TABLE
CREATE TABLE DailyIncome
(
VendorId nvarchar(10),
IncomeDay nvarchar(10),
IncomeAmount int
)
VendorId IncomeDay IncomeAmount
---------- ---------- ------------
SPIKE FRI 100
SPIKE MON 300
FREDS SUN 400
SPIKE WED 500
SPIKE TUE 200
JOHNS WED 900
SPIKE FRI 100
JOHNS MON 300
SPIKE SUN 400
...
SPIKE WED 500
FREDS THU 800
JOHNS TUE 600
PIVOT : To find the average for each vendor
SELECT * FROM DailyIncome
PIVOT
(
AVG (IncomeAmount) FOR IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])
) AS AvgIncomePerDay
Output
VendorId MON TUE WED THU FRI SAT SUN
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
FREDS 500 350 500 800 900 500 400
JOHNS 300 600 900 800 300 800 600
SPIKE 600 150 500 300 200 100 400
PIVOT: Find the max income for each day for vendor SPIKE
SELECT * FROM DailyIncome -- Colums to pivot
PIVOT (
MAX (IncomeAmount) -- Pivot on this column
FOR IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) -- Make colum where IncomeDay is in one of these.
AS MaxIncomePerDay -- Pivot table alias
WHERE VendorId in ('SPIKE') -- Select only for this vendor
Output
VendorId MON TUE WED THU FRI SAT SUN
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
SPIKE 900 200 500 300 300 100 400