Cross-Tab reports in SQL Server 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

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles