title: MySQL Pivot table dates on column name
body: <p>I've been hitting my heat trying to transform this table:</p>
  
  <pre><code> Date         Table       Size in MB
  ------------  ----------- -----------
  2016-09-14    table1      1.02
  2016-09-15    table1      6.03        
  2016-09-14    table2      120.0       
  2016-09-15    table2      150.0       
  2016-09-14    table3      50.0        
  2016-09-15    table3      52.0        
  </code></pre>
  
  <p>Into this:</p>
  
  <pre><code>Table        2016-09-14   2016-09-15   DIFF
  -----------  -----------  -----------  -------
  table1       1.02         6.03          5.01
  table2       120.0        150.0         30.0
  table3       50.0         52.0          2.0
  </code></pre>
  
  <p>A pivot table form the original table but putting the date field into the column name for the size in mb column and if possible do the difference between them on a last column.</p>
  
  <p>So far I could do this</p>
  
  <pre><code>Table       Date1        Date2       
  ----------- -----------  ----------- 
  table1      2016-09-14   2016-09-15
  table2      2016-09-14   2016-09-15
  table3      2016-09-14   2016-09-15
  </code></pre>
  
  <p>using the code from a previous post about pivot tables</p>
  
  <pre><code>select `Table`,
    max(case when rownum = 1 then date end) Date1,
    max(case when rownum = 2 then date end) Date2
  from
  (
    select table_name AS `Table`,
      date,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`,
      @row:=if(@prev=table_name, @row,0) + 1 as rownum,
      @prev:=table_name 
    FROM DBA_DB.table_growth_history, (SELECT @row:=0, @prev:=null) r
    order by table_name, date
  ) s
  group by table_name
  order by table_name, date
  </code></pre>
  
  <p>Not what I want but maybe a step closer. I need help from the experts. I appreciate any advice. Thanks</p>