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

  1. SELECT * FROM dbo.Result
  3. 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.

  1. SELECT * FROM dbo.Result
  2. PIVOT ( count(Id) FOR Result in (Pass, Fail, Promoted) ) AS RESULT
Output

Area Pass Fail Promoted

A 2 1 0
B 1 1 0

  1. SELECT * FROM dbo.Result
  2. 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

  1. SELECT * FROM SALES
  2. PIVOT ( SUM(SaleAmount) FOR Month IN (January, February, March)) AS A
OR
  1. SELECT [January]
  2. , [February]
  3. , [March]
  4. FROM ( SELECT [Month]
  5. , SaleAmount
  6. FROM Sales
  7. ) p PIVOT ( SUM(SaleAmount)
  8. FOR [Month]
  9. IN ([January],[February],[March])
  10. ) AS pvt
Output

January February March

100 200 300

  1. SELECT * FROM SALES
  2. PIVOT ( COUNT(SaleAmount) FOR Month IN (January, February, March)) AS A
Output

January February March

1 1 1

  1. SELECT * FROM SALES
  2. 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

  1. SELECT * FROM T1
  2. PIVOT ( SUM(Value) FOR Date in ([05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]) ) as a
OR
  1. SELECT [No] , [ID] ,[05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]
  2. FROM (
  3. SELECT [No], [ID], [Date], [Value]
  4. FROM T1) up
  5. 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

  1. SELECT * FROM dbo.Result
  2. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Or
  1. SELECT * FROM
  2. ( SELECT * FROM dbo.Result )
  3. AS P
  4. 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?

  1. SELECT * FROM dbo.Result
  2. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Works.
  1. SELECT * FROM dbo.Result
  2. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Error in the following select statement.
  1. SELECT * FROM dbo.Result
  2. where Area = 'A' -- ERROR
  3. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
So we need to combine the results like this:
  1. SELECT * FROM
  2. (SELECT * FROM dbo.Result WHERE Area = 'A') as P
  3. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Output

Area Pass Fail

A 2 1

Notes

The following produces an error:

  1. SELECT * FROM
  2. (SELECT Pass, * FROM dbo.Result WHERE Area = 'A') as P -- ERROR AS Pass IS NOT AVABLE
  3. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
The following works:
  1. SELECT * FROM
  2. (SELECT Id, Area, Result FROM dbo.Result WHERE Area = 'A') as P
  3. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
The following produces an error:
  1. SELECT Id, Area, Result, Pass, Fail FROM -- Error as Id and Result columns are used in pivot
  2. (SELECT Id, Area, Result FROM dbo.Result WHERE Area = 'A') as P
  3. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
The following works:
  1. SELECT Area, Pass, Fail FROM
  2. (SELECT Id, Area, Result FROM dbo.Result WHERE Area = 'A') as P
  3. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Or
  1. SELECT Area, Pass, Fail FROM
  2. (SELECT * FROM dbo.Result WHERE Area = 'A') as P
  3. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
The following works:
  1. SELECT Area AS 'AREA_NAME', Pass AS 'PASS_COUNT', Fail AS 'FAIL_COUNT' -- Columns names are changes
  2. FROM
  3. (SELECT * FROM dbo.Result WHERE Area = 'A') as P
  4. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT
Output

AREA_NAME PASS_COUNT FAIL_COUNT

A 2 1

Example 5

TABLE

  1. SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
  2. FROM Production.Product
PIVOT
  1. SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
  2. FROM
  3. (
  4. SELECT DaysToManufacture, StandardCost
  5. FROM Production.Product
  6. ) AS SourceTable
  7. PIVOT
  8. (
  9. AVG(StandardCost)
  10. FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
  11. ) AS PivotTable;
Example 6

TABLE

  1. SELECT PurchaseOrderID, EmployeeID, VendorID
  2. FROM PurchaseOrderHeader;
PIVOT

  1. SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
  2. FROM
  3. (SELECT PurchaseOrderID, EmployeeID, VendorID
  4. FROM Purchasing.PurchaseOrderHeader) p
  6. PIVOT
  7. (
  8. COUNT (PurchaseOrderID)
  9. FOR EmployeeID IN
  10. ( [250], [251], [256], [257], [260] )
  11. ) AS pvt
  12. 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

  1. CREATE TABLE invoice (
  2. InvoiceNumber VARCHAR(20),
  3. invoiceDate DATETIME,
  4. InvoiceAmount MONEY
  5. )
PIVOT

  1. SELECT *
  2. FROM (
  3. SELECT
  4. year(invoiceDate) as [year],
  6. left(datename(month,invoicedate),3)as [month],
  7. InvoiceAmount as Amount
  8. FROM Invoice
  9. ) as s
  10. PIVOT
  11. (
  12. SUM(Amount) FOR [month] IN (
  13. jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
  14. )
  15. )AS p
EXAMPLE 8

TABLE

  1. CREATE TABLE DailyIncome
  2. (
  4. VendorId nvarchar(10),
  5. IncomeDay nvarchar(10),
  6. IncomeAmount int
  7. )
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

  1. SELECT * FROM DailyIncome
  2. PIVOT
  3. (
  4. AVG (IncomeAmount) FOR IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])
  5. ) 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

  1. SELECT * FROM DailyIncome -- Colums to pivot
  2. PIVOT (
  3. MAX (IncomeAmount) -- Pivot on this column
  4. FOR IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) -- Make colum where IncomeDay is in one of these.
  5. AS MaxIncomePerDay -- Pivot table alias
  6. 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