Cross-Tab reports in SQL Server 2005

July 19, 2005

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. [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

Click for larger image

Fig 1.0

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


Fig 1.1

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


Fig 1.2

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


Fig 1.3

--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


Fig 1.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


Fig 1.5

Conclusion

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

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers