StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Please see the page linked in the answer to <a href="https://stackoverflow.com/questions/7674786/mysql-pivot-table">this question</a>.</p> <p>Note that when you do this, you <em>must</em> specify ahead of time how many columns you want in your output.</p> <p>In response to the comment below, here is a simple/ basic implementation that reproduces the result table above (except for the ID column; having it makes no sense, as each row in the result can summarize more than one row in the input table)</p> <pre><code>SELECT `company_name`, NULLIF(SUM(CASE WHEN `t3`.`year` IS NULL THEN `t3`.`state` ELSE 0 END), 0) AS `null`, NULLIF(SUM(CASE WHEN `t3`.`year` = 2008 THEN `t3`.`state` ELSE 0 END), 0) AS `2008`, NULLIF(SUM(CASE WHEN `t3`.`year` = 2009 THEN `t3`.`state` ELSE 0 END), 0) AS `2009`, NULLIF(SUM(CASE WHEN `t3`.`year` = 2010 THEN `t3`.`state` ELSE 0 END), 0) AS `2010` FROM ( SELECT `t1`.`id`, `t1`.`company_name`, `t1`.`year`, `t1`.`state` FROM `tbl` `t1` WHERE `t1`.`state` = ( SELECT MAX(`state`) FROM `tbl` `t2` WHERE `t2`.`company_name` = `t1`.`company_name` AND (`t2`.`year` IS NULL AND `t1`.`year` IS NULL OR `t2`.`year` = `t1`.`year`) ) ) `t3` GROUP BY `t3`.`company_name`; </code></pre> <p>This uses nested queries: the inner ones (with the <code>t1</code> and <code>t2</code> aliases) find the row with the maximum state for each year and company (and which will break unless you can be sure that this is unique!), and the outer one <code>t3</code> does the pivot.</p> <p>I would test this thoroughly to ensure performance is acceptable on real data.</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