Manipulate results to display rows as columns

问题:

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.

回答1:

You want a PIVOT

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

回答2:

SELECT
  A.DEPTNAME,
  (SELECT COUNT(*) FROM TABLEA A2 INNER JOIN TABLEB B ON B.TableAID = A2.TableAID WHERE B.EMPLOYEE_CLASS = 'Full time' AND A2.TableAID = A.TableAID) FullTimeNo,
  (SELECT COUNT(*) FROM TABLEA A2 INNER JOIN TABLEB B ON B.TableAID = A2.TableAID WHERE B.EMPLOYEE_CLASS = 'Part time' AND A2.TableAID = A.TableAID) PartTimeNo
FROM
  TABLEA

回答3:

Try this:

select A.DEPTNAME, 
       sum(case when B.EMPLOYEE_CLASS='Full time' then B.STAFF_NO else 0) as FullTimeNo,
       sum(case when B.EMPLOYEE_CLASS='Part time' then B.STAFF_NO else 0) as PartTimeNo,
from TabA A
join TabB B on A.DEPTNAME=B.DEPTNAME
group by A.DEPTNAME