SQL Pivot Table

create table table_pivot
(
EmpID varchar(10),
Dept varchar(10)
)

insert into table_pivot
values('A101','IT')

insert into table_pivot
values('A102','IT')

insert into table_pivot
values('B101','Mech')

insert into table_pivot
values('C101','EEE')

insert into table_pivot
values('D101','CS')

insert into table_pivot
values('D102','CS')

insert into table_pivot
values('E101','Chem')

insert into table_pivot
values('F101','Aro')


select * from table_pivot

select EmpID,[IT],[Mech],[EEE],[CS],[Chem],[Aro]

from [dbo].[table_pivot]

pivot

(

count(Dept)

for [Dept] in ([IT],[Mech],[EEE],[CS],[Chem],[Aro])

) as P