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 |
Advertisement
Answer
Use below
x
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