Crosstab report in SQL server (Pivot)

Microsoft introduces new operators PIVOT and UNPIVOT in SQL Server 2005. Traditionally we create queries using the CASE statement and aggregate function in order to produce cross-tab reports. This article illustrates the usage of the new operators, PIVOT and UNPIVOT.
Let us assume that we have a table as described below.
Create table #MyTable
(yearofJoining int,
EmpId int,
Deptid int)
go
insert into #MyTable select 1990,1,1
insert into #MyTable select 1991,2,2
insert into #MyTable select 1990,3,4
insert into #MyTable select 1991,4,2
insert into #MyTable select 1990,5,1
insert into #MyTable select 1990,6,3
insert into #MyTable select 1992,7,3
insert into #MyTable select 1990,8,4
insert into #MyTable select 1993,9,1
insert into #MyTable select 1994,10,2
insert into #MyTable select 1990,11,3
insert into #MyTable select 1995,12,3
insert into #MyTable select 1995,14,3
insert into #MyTable select 1995,15,3
insert into #MyTable select 1995,16,6
go

In order to create a cross tab report, we used to execute the query as described below.
--Original Cross Tab query
select YearofJoining,
count(case [DeptId] when 1 then 1 else null end) as [Department-1],
count(case [DeptId] when 2 then 1 else null end) as [Department-2],
count(case [DeptId] when 3 then 1 else null end) as [Department-3]
from #MyTable where deptid in(1,2,3)
group by Yearofjoining
This would produce the result as shown below.
YearofJoining Department-1 Department-2 Department-3
------------- ------------ ------------ ------------
1990Microsoft introduces new operators PIVOT and UNPIVOT in SQL Server 2005. Traditionally we create queries using the CASE statement and aggregate function in order to produce cross-tab reports. This article illustrates the usage of the new operators, PIVOT and UNPIVOT.
Let us assume that we have a table as described below.
Create table #MyTable
(yearofJoining int,
EmpId int,
Deptid int)
go
insert into #MyTable select 1990,1,1
insert into #MyTable select 1991,2,2
insert into #MyTable select 1990,3,4
insert into #MyTable select 1991,4,2
insert into #MyTable select 1990,5,1
insert into #MyTable select 1990,6,3
insert into #MyTable select 1992,7,3
insert into #MyTable select 1990,8,4
insert into #MyTable select 1993,9,1
insert into #MyTable select 1994,10,2
insert into #MyTable select 1990,11,3
insert into #MyTable select 1995,12,3
insert into #MyTable select 1995,14,3
insert into #MyTable select 1995,15,3
insert into #MyTable select 1995,16,6
go

In order to create a cross tab report, we used to execute the query as described below.
--Original Cross Tab query
select YearofJoining,
count(case [DeptId] when 1 then 1 else null end) as [Department-1],
count(case [DeptId] when 2 then 1 else null end) as [Department-2],
count(case [DeptId] when 3 then 1 else null end) as [Department-3]
from #MyTable where deptid in(1,2,3)
group by Yearofjoining
This would produce the result as shown below.
YearofJoining Department-1 Department-2 Department-3
------------- ------------ ------------ ------------
1990      2 0 2
1991 0 2 0
1992 0 0 1
1993 1 0 0
1994 0 1 0
1995 0 0 3


The same results can be reproduced using the operator, PIVOT.
--New PIVOT Operator in SQL 2005
SELECT YearofJoining, [1] as [Department-1],[2] as [Department-2],
[3] as [Department-3] FROM
(SELECT YearOfJoining,Deptid from #MyTable) p
PIVOT
( Count(DeptId) for DEPTID in ([1],[2],[3]))
AS pvt
ORDER BY Yearofjoining
Now let us assume the we have have a table as decribed below.
create table #MyTable2 (BatchID int ,Status int)
go
insert into #MyTable2 select 1001 ,1
insert into #MyTable2 select 1001 ,2
insert into #MyTable2 select 1002 ,0
insert into #MyTable2 select 1002 ,3
insert into #MyTable2 select 1002 ,4
insert into #MyTable2 select 1003 ,4
insert into #MyTable2 select 1004 ,4
go


In order to create a cross tab report, we used to execute the query as described below.
--Original Cross Tab query
select batchid,
sum(case status when 0 then 1 else 0 end) as [status-0],
sum(case status when 1 then 1 else 0 end) as [status-1],
sum(case status when 2 then 1 else 0 end) as [status-2],
sum(case status when 3 then 1 else 0 end) as [status-3],
sum(case status when 4 then 1 else 0 end) as [status-4]
from #MyTable2
group by batchid
This would produce the result as shown below.
BatchId Status-0 Status-1 Status-2 Status-3 Status-4
----------- ----------- ----------- ----------- ----------- -----------
1001 0 1 1 0 0
1002 1 0 0 1 1
1003 0 0 0 0 1
1004 0 0 0 0 1



--New PIVOT Operator in SQL 2005
SELECT BatchId, [0]as [Status-0],
[1]as [Status-1],
[2] as [Status-2],
[3]as [Status-3],
[4]as [Status-4]
FROM
(SELECT BatchId,status from #MyTable2) p
PIVOT
( count(Status) for status in ([0],[1],[2],[3],[4]))
AS pvt
ORDER BY BatchId
Notice, in the traditional cross tab query I am using the aggregate function sum and in the new PIVOT query I am using count.
Let us try using the aggregate function sum in the new PIVOT query.
--New PIVOT Operator in SQL 2005
SELECT BatchId, [0]as [Status-0],
[1]as [Status-1],
[2] as [Status-2],
[3]as [Status-3],
[4]as [Status-4]
FROM
(SELECT BatchId,status from #MyTable2) p
PIVOT
(sum(Status) for status in ([0],[1],[2],[3],[4]))
AS pvt
ORDER BY BatchId
You would get the result as described below.
BatchId Status-0 Status-1 Status-2 Status-3 Status-4
----------- ----------- ----------- ----------- ----------- -----------
1001 NULL 1 2 NULL NULL
1002 0 NULL NULL 3 4
1003 NULL NULL NULL NULL 4
1004 NULL NULL NULL NULL 4

As you see, when we use the new operator PIVOT with the same aggregate function we will not get the desired results. Instead of getting the Yes/No status we get the actual status value.
Let's assume we have a table that looks like the cross tab report described below.
create table #mycrosstab(
BatchId int, [Status-0] int, [Status-1] int,
[Status-2] int, [Status-3] int, [Status-4] int)
go
insert into #mycrosstab select 1001,0,1, 1, 0, 0
insert into #mycrosstab select 1002,1,0, 0, 1, 1
insert into #mycrosstab select 1003,0,0, 0, 0, 1
insert into #mycrosstab select 1004,0,0, 0, 0, 1
go
Let's try to reverse the crosstab report in order to get the original table using the new operator UNPIVOT.
SELECT BatchId, Status,StatusValue
FROM
(SELECT BatchId , [Status-0] , [Status-1] , [Status-2] , [Status-3], [Status-4]
FROM #mycrosstab ) p
UNPIVOT
(StatusValue FOR status IN
( [Status-0] , [Status-1] , [Status-2] , [Status-3], [Status-4])
)AS unpvt

BatchId Status StatusValue
1001 Status-0 0
1001 Status-1 1
1001 Status-2 1
1001 Status-3 0
1001 Status-4 0
1002 Status-0 1
1002 Status-1 0
1002 Status-2 0
1002 Status-3 1
1002 Status-4 1
1003 Status-0 0
1003 Status-1 0
1003 Status-2 0
1003 Status-3 0
1003 Status-4 1
1004 Status-0 0
1004 Status-1 0
1004 Status-2 0
1004 Status-3 0
1004 Status-4 1
Using the case statement and UNPIVOT operator, you can bring back the original table as described below
SELECT BatchId, MyStatus= case
when status= 'Status-0' and statusvalue =1 then '0'
when status= 'Status-1' and statusvalue =1 then '1'
when status= 'Status-2' and statusvalue =1 then '2'
when status= 'Status-3' and statusvalue =1 then '3'
when status= 'Status-4' and statusvalue =1 then '4' end
,StatusValue
FROM
(SELECT BatchId , [Status-0] , [Status-1] , [Status-2] , [Status-3], [Status-4]
FROM #mycrosstab ) p
UNPIVOT
(StatusValue FOR status IN
( [Status-0] , [Status-1] , [Status-2] , [Status-3], [Status-4])
)AS unpvt
BatchId MyStatus StatusValue
1001 NULL 0
1001 1 1
1001 2 1
1001 NULL 0
1001 NULL 0
1002 0 1
1002 NULL 0
1002 NULL 0
1002 3 1
1002 4 1
1003 NULL 0
1003 NULL 0
1003 NULL 0
1003 NULL 0
1003 4 1
1004 NULL 0
1004 NULL 0
1004 NULL 0
1004 NULL 0
1004 4 1

2 0 2
1991 0 2 0
1992 0 0 1
1993 1 0 0
1994 0 1 0
1995 0 0 3


The same results can be reproduced using the operator, PIVOT.
--New PIVOT Operator in SQL 2005
SELECT YearofJoining, [1] as [Department-1],[2] as [Department-2],
[3] as [Department-3] FROM
(SELECT YearOfJoining,Deptid from #MyTable) p
PIVOT
( Count(DeptId) for DEPTID in ([1],[2],[3]))
AS pvt
ORDER BY Yearofjoining
Now let us assume the we have have a table as decribed below.
create table #MyTable2 (BatchID int ,Status int)
go
insert into #MyTable2 select 1001 ,1
insert into #MyTable2 select 1001 ,2
insert into #MyTable2 select 1002 ,0
insert into #MyTable2 select 1002 ,3
insert into #MyTable2 select 1002 ,4
insert into #MyTable2 select 1003 ,4
insert into #MyTable2 select 1004 ,4
go


In order to create a cross tab report, we used to execute the query as described below.
--Original Cross Tab query
select batchid,
sum(case status when 0 then 1 else 0 end) as [status-0],
sum(case status when 1 then 1 else 0 end) as [status-1],
sum(case status when 2 then 1 else 0 end) as [status-2],
sum(case status when 3 then 1 else 0 end) as [status-3],
sum(case status when 4 then 1 else 0 end) as [status-4]
from #MyTable2
group by batchid
This would produce the result as shown below.
BatchId Status-0 Status-1 Status-2 Status-3 Status-4
----------- ----------- ----------- ----------- ----------- -----------
1001 0 1 1 0 0
1002 1 0 0 1 1
1003 0 0 0 0 1
1004 0 0 0 0 1



--New PIVOT Operator in SQL 2005
SELECT BatchId, [0]as [Status-0],
[1]as [Status-1],
[2] as [Status-2],
[3]as [Status-3],
[4]as [Status-4]
FROM
(SELECT BatchId,status from #MyTable2) p
PIVOT
( count(Status) for status in ([0],[1],[2],[3],[4]))
AS pvt
ORDER BY BatchId
Notice, in the traditional cross tab query I am using the aggregate function sum and in the new PIVOT query I am using count.
Let us try using the aggregate function sum in the new PIVOT query.
--New PIVOT Operator in SQL 2005
SELECT BatchId, [0]as [Status-0],
[1]as [Status-1],
[2] as [Status-2],
[3]as [Status-3],
[4]as [Status-4]
FROM
(SELECT BatchId,status from #MyTable2) p
PIVOT
(sum(Status) for status in ([0],[1],[2],[3],[4]))
AS pvt
ORDER BY BatchId
You would get the result as described below.
BatchId Status-0 Status-1 Status-2 Status-3 Status-4
----------- ----------- ----------- ----------- ----------- -----------
1001 NULL 1 2 NULL NULL
1002 0 NULL NULL 3 4
1003 NULL NULL NULL NULL 4
1004 NULL NULL NULL NULL 4

As you see, when we use the new operator PIVOT with the same aggregate function we will not get the desired results. Instead of getting the Yes/No status we get the actual status value.
Let's assume we have a table that looks like the cross tab report described below.
create table #mycrosstab(
BatchId int, [Status-0] int, [Status-1] int,
[Status-2] int, [Status-3] int, [Status-4] int)
go
insert into #mycrosstab select 1001,0,1, 1, 0, 0
insert into #mycrosstab select 1002,1,0, 0, 1, 1
insert into #mycrosstab select 1003,0,0, 0, 0, 1
insert into #mycrosstab select 1004,0,0, 0, 0, 1
go
Let's try to reverse the crosstab report in order to get the original table using the new operator UNPIVOT.
SELECT BatchId, Status,StatusValue
FROM
(SELECT BatchId , [Status-0] , [Status-1] , [Status-2] , [Status-3], [Status-4]
FROM #mycrosstab ) p
UNPIVOT
(StatusValue FOR status IN
( [Status-0] , [Status-1] , [Status-2] , [Status-3], [Status-4])
)AS unpvt

BatchId Status StatusValue
1001 Status-0 0
1001 Status-1 1
1001 Status-2 1
1001 Status-3 0
1001 Status-4 0
1002 Status-0 1
1002 Status-1 0
1002 Status-2 0
1002 Status-3 1
1002 Status-4 1
1003 Status-0 0
1003 Status-1 0
1003 Status-2 0
1003 Status-3 0
1003 Status-4 1
1004 Status-0 0
1004 Status-1 0
1004 Status-2 0
1004 Status-3 0
1004 Status-4 1
Using the case statement and UNPIVOT operator, you can bring back the original table as described below
SELECT BatchId, MyStatus= case
when status= 'Status-0' and statusvalue =1 then '0'
when status= 'Status-1' and statusvalue =1 then '1'
when status= 'Status-2' and statusvalue =1 then '2'
when status= 'Status-3' and statusvalue =1 then '3'
when status= 'Status-4' and statusvalue =1 then '4' end
,StatusValue
FROM
(SELECT BatchId , [Status-0] , [Status-1] , [Status-2] , [Status-3], [Status-4]
FROM #mycrosstab ) p
UNPIVOT
(StatusValue FOR status IN
( [Status-0] , [Status-1] , [Status-2] , [Status-3], [Status-4])
)AS unpvt
BatchId MyStatus StatusValue
1001 NULL 0
1001 1 1
1001 2 1
1001 NULL 0
1001 NULL 0
1002 0 1
1002 NULL 0
1002 NULL 0
1002 3 1
1002 4 1
1003 NULL 0
1003 NULL 0
1003 NULL 0
1003 NULL 0
1003 4 1
1004 NULL 0
1004 NULL 0
1004 NULL 0
1004 NULL 0
1004 4 1

Share: