$query = qq{ SELECT country, loc ,SUM(CASE WHEN dept = 'pers' AND category = 'consultant' AND gender = 'm' THEN salary ELSE NULL END ) AS 'pers#consultant#m' ,SUM(CASE WHEN dept = 'pers' AND category = 'consultant' AND gender = 'f' THEN salary ELSE NULL END ) AS 'pers#consultant#f' ,SUM(CASE WHEN dept = 'pers' AND category = 'contractor' AND gender = 'm' THEN salary ELSE NULL END ) AS 'pers#contractor#m' ,SUM(CASE WHEN dept = 'pers' AND category = 'contractor' AND gender = 'f' THEN salary ELSE NULL END ) AS 'pers#contractor#f' ,SUM(CASE WHEN dept = 'pers' AND category = 'employee' AND gender = 'm' THEN salary ELSE NULL END ) AS 'pers#employee#m' ,SUM(CASE WHEN dept = 'pers' AND category = 'employee' AND gender = 'f' THEN salary ELSE NULL END ) AS 'pers#employee#f' ,SUM(CASE WHEN dept = 'dev' AND category = 'consultant' AND gender = 'm' THEN salary ELSE NULL END ) AS 'dev#consultant#m' ,SUM(CASE WHEN dept = 'dev' AND category = 'consultant' AND gender = 'f' THEN salary ELSE NULL END ) AS 'dev#consultant#f' ,SUM(CASE WHEN dept = 'dev' AND category = 'contractor' AND gender = 'm' THEN salary ELSE NULL END ) AS 'dev#contractor#m' ,SUM(CASE WHEN dept = 'dev' AND category = 'contractor' AND gender = 'f' THEN salary ELSE NULL END ) AS 'dev#contractor#f' ,SUM(CASE WHEN dept = 'dev' AND category = 'employee' AND gender = 'm' THEN salary ELSE NULL END ) AS 'dev#employee#m' ,SUM(CASE WHEN dept = 'dev' AND category = 'employee' AND gender = 'f' THEN salary ELSE NULL END ) AS 'dev#employee#f' ,SUM(CASE WHEN dept = 'sales' AND category = 'consultant' AND gender = 'm' THEN salary ELSE NULL END ) AS 'sales#consultant#m' ,SUM(CASE WHEN dept = 'sales' AND category = 'consultant' AND gender = 'f' THEN salary ELSE NULL END ) AS 'sales#consultant#f' ,SUM(CASE WHEN dept = 'sales' AND category = 'contractor' AND gender = 'm' THEN salary ELSE NULL END ) AS 'sales#contractor#m' ,SUM(CASE WHEN dept = 'sales' AND category = 'contractor' AND gender = 'f' THEN salary ELSE NULL END ) AS 'sales#contractor#f' ,SUM(CASE WHEN dept = 'sales' AND category = 'employee' AND gender = 'm' THEN salary ELSE NULL END ) AS 'sales#employee#m' ,SUM(CASE WHEN dept = 'sales' AND category = 'employee' AND gender = 'f' THEN salary ELSE NULL END ) AS 'sales#employee#f' ,SUM(salary) AS total FROM person INNER JOIN depts ON (person.dept_id=depts.dept_id) INNER JOIN categories ON (person.cat_id=categories.cat_id) INNER JOIN locs on(locs.loc_id=person.loc_id) INNER JOIN countries ON(locs.country_id=countries.country_id) GROUP BY country, loc }