title: MySQL pivot table query with dynamic columns
body: <p>I'm using the following tables for storing product data:</p>
  
  <pre><code>mysql&gt; SELECT * FROM product;
  +---------------+---------------+--------+
  | id | name     | description   | stock  |
  +---------------+---------------+--------+
  |  1 | product1 | first product |    5   | 
  |  2 | product2 | second product|    5   | 
  +---------------+---------------+--------+
  
  mysql&gt; SELECT * FROM product_additional;
  +-----------------+------------+
  | id | fieldname  | fieldvalue |
  +-----------------+------------+
  |  1 | size       | S          |
  |  1 | height     | 103        |
  |  2 | size       | L          |
  |  2 | height     | 13         |
  |  2 | color      | black      |
  +-----------------+------------+
  </code></pre>
  
  <p>Using the following query to select the records from both tables</p>
  
  <pre><code>mysql&gt; SELECT 
      p.id
      , p.name
      , p.description
      ,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) as `size`
      ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) as `height`
      ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) as `color`
  FROM product p
  LEFT JOIN product_additional AS pa ON p.id = pa.id
  GROUP BY p.id
  +---------------+---------------+--------+---------+--------+
  | id | name     | description   | size   | height  | color  |
  +---------------+---------------+--------+---------+--------+
  |  1 | product1 | first product | S      | 103     | null   |
  |  2 | product2 | second product| L      | 13      | black  |
  +---------------+---------------+--------+---------+--------+
  </code></pre>
  
  <p>And everything is working correctly :) </p>
  
  <p>Because i fill the 'additional' table dynamically it would be nice, if the query would also be dynamic. In that way i dont have to change the query everytime i put in a new fieldname and fieldvalue.</p>