Cross-Tab reports in SQL Server 2005

Let us assume that we have a table as described below. [Refer Fig 1.0]

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. [Refer Fig 1.1]

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. [Refer Fig 1.2]

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. [Refer Fig 1.3]

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. [Refer Fig 1.4]

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. [Fig 1.3]

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. [Refer Fig 1.5]

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

Conclusion

This article has illustrated the usage of the new operators, PIVOT and UNPIVOT, Introduced by Microsoft in SQL Server 2005.