Pivot query in sql server

Steps 

1. Create the required tables
2. Employees and department as given in script
3. create the as string and run it using Exec( ) function

• Required Output

create table Department
(
 DepartmentID int primary key identity,
 DepartmentName varchar(200)
)

create table Employee
(
  EmployeeID int primary key identity,
  EmpName varchar(200),
  Address varchar(200),
  DepartmentID int,
  constraint fk_dept_emp foreign key(DepartmentID) references Department

(departmentID)
)

insert into Department values('Physics')
insert into Department values('Biology')
insert into Department values('Chemistry')
insert into Department values('Computer Science')

insert into Employee values('Ahmad','Add 1',1)
insert into Employee values('Mahmood','Add 2',1)
insert into Employee values('Zahir','Add 3',1)
insert into Employee values('Aziz','Add 4',1)
insert into Employee values('Khalid','Add 5',2)
insert into Employee values('Mirwaise','Add 6',2)
insert into Employee values('Inayat','Add 2',2)
insert into Employee values('Jamil','Add 3',3)
insert into Employee values('Bari','Add 4',3)
insert into Employee values('Qureshi','Add 1',3)
insert into Employee values('Farooq','Add 9',3)
insert into Employee values('Qazi','Add 8',3)
insert into Employee values('massod','Add 13',3)
insert into Employee values('Fahim','Add 8',3)

• Required pivot query (dynamic). if departments get increases. automatically will add to the query output.

DECLARE @columnscsv VARCHAR(MAX)
declare @sql varchar(max)
SELECT @columnscsv = COALESCE(@columnscsv + '],[','') + departmentName
 FROM department
SET @columnscsv = '[' + @columnscsv + ']'
set @sql='select * from (
SELECT     ''No of Employees'' as Employees,Department.DepartmentName, Employee.EmployeeID
FROM         Department INNER JOIN
                      Employee ON Department.DepartmentID = Employee.DepartmentID
) as s
pivot
(
  count(EmployeeID) for DepartmentName in('+@columnscsv+')
) as PVT'
Exec(@sql)