How to create MySQL crosstab with NULLs

Question

I have a database with Jobs, Companies, and Salaries that contain the amounts each company pays the employees in each job. Each Company, Job, and Salary is linked to a Survey. I'd like the output something for a Survey to look like this:

|      |Company A|Company B|Company C|
+------+---------+---------+---------+
|Job A |  34000  |  36750  |  41000  |
|Job B |  65880  |         |  67000  |
|Job C |         |  52340  |  49000  |

The empty cells are the ones where there is no entry in the Salaries table matching the company to the job.

I am having trouble making sense of the crosstab examples I've seen online. I think I have to generate a data set that includes a row for each combination, including nulls, like this:

Company A | Job A | 34000
Company A | Job B | 65880
Company A | Job C | NULL
Company B | Job A | 36750
Company B | Job B | NULL
Company B | Job C | 52340
Company C | Job A | 41000
Company C | Job B | 67000
Company C | Job C | 49000

I'm running this query to generate the above rows and it's working OK:

SELECT C.companyname, J.jobtitle, S.salary FROM Companies C INNER JOIN Jobs J 
  ON (C.surveyid=J.surveyid) LEFT JOIN Salaries S
  ON (J.jobid=S.jobid and C.companyid=S.companyid)
  ORDER BY C.companyname, J.jobtitle

You can see it here: http://sqlfiddle.com/#!2/91e49/12/0

Now that I'm getting the correct data, how do I turn this into a crosstab?

Can you set up a SQL Fiddle with sample data sets? If you want to include NULL values, they just need to be LEFT JOIN'd on a non-null value. — doublesharp
So maybe I'm misunderstanding the LEFT JOIN altogether--I want there to be NULL salary values when there is NO matching record in the Salaries table. I'll go check out SQL Fiddle... I've only ever used JS Fiddle so I'll assume it's similar. — iopener
@iopener . . . I am confused about what you are asking. Your question is about a cross tab. Your query and subsequent comments are about missing rows in joins. Can you provide some sample data and desired output? — Gordon Linoff
@doublesharp SQL Fiddle is here: sqlfiddle.com/#!2/91e49/2/0 and I can already see there is something wrong with my plan, as it's joining every company with every job. — iopener

Gordon Linoff Gordon Linoff · Accepted Answer · 2013-11-17T16:48:26

You can create the cross-tab explicitly by using aggregation after joining the tables together. This uses conditional aggregation (that is, case statements nested in aggregation functions).

The following works when you know the job titles for the columns:

SELECT C.companyname,
       max(case when jobtitle = 'Job A' then S.salary end) as JobA,
       max(case when jobtitle = 'Job B' then S.salary end) as JobB,
       max(case when jobtitle = 'Job C' then S.salary end) as JobC
FROM Companies C INNER JOIN
     Jobs J 
     ON C.surveyid = J.surveyid JOIN
     Salaries S
     ON J.jobid = S.jobid and C.companyid = S.companyid
GROUP BY C.companyname;

If you want to write something more general, that reads the job titles from the Jobs table, then you have to construct the query as a string and use a prepare statement to execute it.

How to create MySQL crosstab with NULLs

1 Answers