Thursday, November 20, 2008

Crosstab report in SQL server (Pivot)

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

1 comments:

Andreya padty said...

Hi Nice Blog .Personally I don't see a lot of nice possibilities around Freelance Search Engine Optimization for 2008. For the Rat it will be a hard job to digg niches in the corn-bins of Web2.0. Especially those ones with a high Page Ranks and top social bookmarking scores seo india.