CREATE TABLE #PivotSample (DeptId int
      ,EmpName varchar(100)
      ,DeptName varchar(50))
    
GO

INSERT INTO #PivotSample
SELECT 1,'James','DotNet'
UNION ALL
SELECT 1,'Jacob','DotNet'
UNION ALL
SELECT 3,'Jan','Java'
UNION ALL
SELECT 4,'Doe','PHP'
UNION ALL
SELECT 3,'John','Java'
GO

--Select Data from table
SELECT
DeptId,
EmpName,
DeptName
from #PivotSample

--Now Select aggregated data using PIVOT (convert rows into columns)
SELECT
 *
FROM (   
  SELECT DeptId,  EmpName,  DeptName    FROM #PivotSample
  ) AS P
  PIVOT( Count(EmpName) FOR DeptName IN (DotNet, Java, PHP)) AS pv

GO