sql - Manipulate results to display rows as columns

Question

I have two tables. Table A contains a list of departments, there are 20 in total.

See image

Table B contains at least 1 row per department found in Table A, with some containing a few.

See image

What I want is a 3rd table created from A & B which basically lists every department and then the number of people who are full time and part time. If for example there is a department found in table b which only has a figure for either Full or Part time (an example of this is department D) then I want the table to display 0 (zero) instead of leaving this blank.

See image

Can anyone advise on this?

EDIT If there is no for example 'Part time' for one of the Departments, that means that their part time staff count WILL be zero as a rule.

Answer 1

You want a PIVOT

select *
from (tableb) s
pivot (Max(staffno) for employee_class in ([Full Time],[Part Time])) p