Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 19, 2005

Cross-Tab reports in SQL Server 2005

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM