StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to create MySQL crosstab with NULLs
text
Body
copied!<p>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:</p> <pre><code>| |Company A|Company B|Company C| +------+---------+---------+---------+ |Job A | 34000 | 36750 | 41000 | |Job B | 65880 | | 67000 | |Job C | | 52340 | 49000 | </code></pre> <p>The empty cells are the ones where there is no entry in the Salaries table matching the company to the job.</p> <p>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:</p> <pre><code>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 </code></pre> <p>I'm running this query to generate the above rows and it's working OK:</p> <pre><code>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 </code></pre> <p>You can see it here: <a href="http://sqlfiddle.com/#!2/91e49/12/0" rel="nofollow">http://sqlfiddle.com/#!2/91e49/12/0</a></p> <p>Now that I'm getting the correct data, how do I turn this into a crosstab?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload