CREATE TABLE StaffHistory 
  (DeptName varchar(40)NOT NULL, EmpID int NOT NULL, StartDate datetime NOT NULL, EndDate datetime NULL);
  INSERT INTO StaffHistory 
  VALUES 
  ('Engineering', 1, '2003-12-16 00:00:00.000', null),
  ('Tool Design', 2, '2003-07-01 00:00:00.000', null),
  ('Sales', 3, '2003-11-01 00:00:00.000', null),
  ('Engineering', 4, '2002-07-01 00:00:00.000', null),
  ('Tool Design', 5, '2002-07-01 00:00:00.000', null),
  ('Sales', 6, '2002-11-01 00:00:00.000', null),
  ('Engineering', 7, '2001-07-01 00:00:00.000', null),
  ('Tool Design', 8, '2001-07-01 00:00:00.000', null),
  ('Sales', 9, '2001-11-01 00:00:00.000', null);
  

SELECT DeptName
	, [2001]
	, [2002]
	, [2003]
	, [All]
FROM (
	SELECT DeptName
		, startyear = isnull(cast(year(StartDate) AS CHAR(4)), 'All')
		, Cnt = Count(empid)
	FROM StaffHistory
	GROUP BY DeptName
		, year(StartDate)
	WITH rollup
	) src
pivot(sum(Cnt) FOR startyear IN (
			[2001]
			, [2002]
			, [2003]
			, [All]
			)) pvt
WHERE DeptName IS NOT NULL

SELECT DeptName,
	SUM( CASE WHEN YEAR( StartDate) = 2001 THEN 1 ELSE 0 END) [2001],
	SUM( CASE WHEN YEAR( StartDate) = 2002 THEN 1 ELSE 0 END) [2002],
	SUM( CASE WHEN YEAR( StartDate) = 2003 THEN 1 ELSE 0 END) [2003],
	COUNT(*) [All]
FROM StaffHistory
GROUP BY DeptName