StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You need to pivot the table but mysql does not have any such functionality of pivot</p> <p>so we need to replicate its functionality</p> <p><strong>EDITED</strong></p> <pre><code>Select group_concat( DISTINCT if(year is null, CONCAT('max(if (year is null, state, 0)) as ''NULL'' '), CONCAT('max(if (year=''', year, ''', state, 0)) as ''',year, ''' ')) ) into @sql from tbl join (SELECT @sql:='')a; set @sql = concat('select company_name, ', @sql, 'from tbl group by company_name;'); PREPARE stmt FROM @sql; EXECUTE stmt; </code></pre> <p><strong>Result</strong></p> <pre><code>| COMPANY_NAME | 2008 | 2009 | 2010 | NULL | -------------------------------------------- | companyA | 1 | 0 | 0 | 0 | | companyB | 0 | 2 | 0 | 0 | | companyC | 0 | 0 | 3 | 3 | </code></pre> <h2><strong><a href="http://sqlfiddle.com/#!2/a013c/22" rel="nofollow">SQL FIDDLE</a></strong></h2> <p>There are 2 approaches to solve your problem 1. create case for each year, which is not possible in your case as we are dealing with year 2. generate the query dynamically so that we get proper columns as per your need.</p> <p>I have given solution according to the second solution where I am generating the query and storing it in <code>@sql</code> variable. In the fiddle I have printed the contents of <code>@sql</code> before executing it.</p> <pre><code>select company_name, max(if (year='2008', state, 0)) as '2008' ,max(if (year='2009', state, 0)) as '2009' ,max(if (year='2010', state, 0)) as '2010' ,max(if (year is null, state, 0)) as 'NULL' from tbl group by company_name; </code></pre> <p>For more information regarding <code>group_concat()</code> go through the link <strong><a href="http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php" rel="nofollow">GROUP_CONCAT</a></strong> and <strong><a href="http://dev.mysql.com/doc/refman/5.0/en/user-variables.html" rel="nofollow">USER DEFINED VARIABLE</a></strong></p> <p>Hope this helps..</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