MSSQL Cross-Tab Query

Example Script for MSSQL Cross-Tab Query

Create table #Table
(
yearofJoining int,
EmpId int,
Deptid int
)
GO


insert into #Table select 1990,1,1
insert into #Table select 1991,2,2
insert into #Table select 1990,3,4
insert into #Table select 1991,4,2
insert into #Table select 1990,5,1
insert into #Table select 1990,6,3
insert into #Table select 1992,7,3
insert into #Table select 1990,8,4
insert into #Table select 1993,9,1
insert into #Table select 1994,10,2
insert into #Table select 1990,11,3
insert into #Table select 1995,12,3
insert into #Table select 1995,14,3
insert into #Table select 1995,15,3
insert into #Table select 1995,16,6
GO

--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 #Table where deptid in(1,2,3)
group by Yearofjoining

Out put:

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

PIVOT Example:


SELECT YearofJoining, [1] as [Department-1],[2] as [Department-2],
[3] as [Department-3] FROM
(SELECT YearOfJoining,Deptid from #Table) p
PIVOT
( Count(DeptId) for DEPTID in ([1],[2],[3]))
AS pvt
ORDER BY Yearofjoining