CODEWITHSUNDEEP
sqlt-sql
JsonStatham
JsonStatham

Reputation: 10364

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

enter image description here

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

See image

enter image description here

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

enter image description here

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.

Upvotes: 3

Views: 162

Answers (3)

Robert
Robert

Reputation: 25753

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

Upvotes: 0

Nathan
Nathan

Reputation: 503

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

Upvotes: 0

podiluska
podiluska

Reputation: 51494

You want a PIVOT

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

Upvotes: 3

Related Questions