select university, hospital,
sum(case when category='Admin' then memcount else 0 end) as Admin_staff_total,
sum(case when category='Clinical' then memcount else 0 end) as Clinical_staff_total,
sum(case when category='ResearchStaff' then memcount else 0 end) as Research_staff_total,
sum(case when category='Coursework' then memcount else 0 end) as Coursework_stud_total,
sum(case when category='ResearchStudent' then memcount else 0 end) as Res_stud_total 
from
(
select university, hospital, 
a.number_of_staff as memcount,'Admin' as Category
from Admin_staff_at_Hospital a 
WHERE hospital = '$hospital'

UNION ALL

select university, hospital, c.number_of_staff,'Clinical'
from Clinical_staff_at_Hospital c 
WHERE hospital = '$hospital'

UNION ALL

select university, hospital, r.number_of_staff,'ResearchStaff'
from Research_staff_at_Hospital r 
WHERE hospital = '$hospital'

UNION ALL

select university, hospital, cs.number_of_students,'Coursework'
from Coursework_students_at_Hospital cs 
WHERE hospital = '$hospital' 

UNION ALL

select university, hospital, rs.number_of_students,'ResearchStudent'
from Research_students_at_Hospital rs 
WHERE hospital = '$hospital'
)t
group by university, hospital