Post
Ask Question

Pivot Examples in SQL Server

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  
  2.   
  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  
  5.   
  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],  
  5.   
  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. (  
  3.   
  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


Similar Articles