Pivot Examples in SQL Server

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.

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