Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Cross-Tab or PIVOT in SQL-Server2005

March 7 2009 , Written by Balavardhan Published on #SQL Server2005


Cross-Tab or PIVOT in SQL-Server2005

With the new PIVOT operator in SQL Server 2005, the CASE statement and GROUP BY statements are no longer necessary. (Every PIVOT query involves an aggregation of some type, so you can omit the GROUP BY statement.) The PIVOT operator provides the same functionality that we tried to achieve with the CASE statement query, but you can achieve it through less code.


EX:

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



In order to create a cross tab report, we used to execute the query as described below.


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

You would get the result as described below.


Share this post

Repost 0

Comment on this post