Pivot Examples in SQL Server

Mahesh Alle

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

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

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

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

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

SQL ServerSQL PivotSQL PIVOT ExamplesSQL PIVOT Statement

2 Comments

Join the conversation! Your thoughts help the community grow.

ib bellad11y ago
How to display one week(day by day) attendance report of student
ib bellad11y ago
Nice..,How to display one week attendance of student

Pivot Examples in SQL Server

Mahesh Alle

7y127.2k2100Article

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

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

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

SELECT * FROM T1
PIVOT ( SUM(Value) FOR Date in ([05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]) ) as a

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

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

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

SQL ServerSQL PivotSQL PIVOT ExamplesSQL PIVOT Statement

2 Comments

Join the conversation! Your thoughts help the community grow.

ib bellad11y ago
How to display one week(day by day) attendance report of student
ib bellad11y ago
Nice..,How to display one week attendance of student