How to find employee department changes?

I have a table that has the old departments and new departments of various employees:

EmployeeID	OldDept	NewDept
123	Design	Design
234	Software	Engineering
345	Design	Software
456	Advertising	Software

How can I create a table that looks like this:

Dept	TransfersIn	TransfersOut
Software	2	1
Design	0	1
Advertising	0	1
Engineering	1	0

Answer

Use below

select * from (
  select NewDept as Department, count(*) Transfers, 'TransfersIn' Mode from your_table
  where NewDept != OldDept
  group by Department, Mode
  union all
  select OldDept as Department, count(*) Transfers, 'TransfersOut' Mode from your_table
  where NewDept != OldDept
  group by Department, Mode
)
pivot (sum(Transfers) for mode in ('TransfersIn', 'TransfersOut'))   

 

if applied to sample data in your question – output is