title: MySQL column names as field names for a many to many relationship with a junction table
body: <p>bit of a complicated one here. I've been looking around at a way to use results as column names, and whilst it seems possible, all the examples I've found use a very simple table design.</p>
  
  <p>What I've got is 3 tables.</p>
  
  <p>We have an 'order' table, which can then have 'order_extras'. The 'extras' table stores the names and prices of the extras, and the 'order_extras' basically cointains a primary key, the order id and the extra id. </p>
  
  <p>A rough graphical representation of this is as follows:<img src="https://i.stack.imgur.com/KGzIF.png" alt="enter image description here"></p>
  
  <p>Using this as an example, lets assume the 'extras' table is populated with 3 extra items, the name and price are irrelevant at this stage.</p>
  
  <p>What I want to do, is get all the orders, but with extra columns for the name of each extra item. If the item has been purchased (aka linked in the order_extras table) it'll show the price, otherwise it'll be empty/null.</p>
  
  <p>Is this even possible? I've been looking into pivot tables but the information on this sort of thing seems a bit shaky. Any info or suggestions would be greatly appreciated!</p>
  
  <p><strong>Example data</strong></p>
  
  <p>Extras:</p>
  
  <pre><code>+----+------------------+--------+
  | id |       name       | price  |
  +----+------------------+--------+
  |  1 | Insurance        | 59.95  |
  |  2 | Lifetime Updates | 79.95  |
  |  3 | Phone Support    | 124.95 |
  +----+------------------+--------+
  </code></pre>
  
  <p>Orders:</p>
  
  <pre><code>+----+------------+
  | id |  customer  |
  +----+------------+
  |  1 | John Smith |
  |  2 | Bob Newbie |
  |  3 | Bill Jobs  |
  |  4 | Ray Stantz |
  +----+------------+
  </code></pre>
  
  <p>order_extras:</p>
  
  <pre><code>+----+----------+----------+
  | id | order_id | extra_id |
  +----+----------+----------+
  |  1 |        4 |        2 |
  |  2 |        3 |        1 |
  |  3 |        3 |        3 |
  |  4 |        1 |        1 |
  +----+----------+----------+
  </code></pre>
  
  <p>Desired Output:</p>
  
  <pre><code>+----------+----------------+-----------+------------------+---------------+
  | order.id | order.customer | Insurance | Lifetime Updates | Phone Support |
  +----------+----------------+-----------+------------------+---------------+
  |        1 | John Smith     | 59.95     | 0                | 0             |
  |        2 | Bob Newbie     | 0         | 0                | 0             |
  |        3 | Bill Jobs      | 59.95     | 0                | 124.95        |
  |        4 | Ray Stantz     | 0         | 79.95            | 0             |
  +----------+----------------+-----------+------------------+---------------+
  </code></pre>