MySQL column names as field names for a many to many relationship with a junction table

Question

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.

What I've got is 3 tables.

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.

A rough graphical representation of this is as follows: enter image description here

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.

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.

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!

Example data

Extras:

+----+------------------+--------+
| id |       name       | price  |
+----+------------------+--------+
|  1 | Insurance        | 59.95  |
|  2 | Lifetime Updates | 79.95  |
|  3 | Phone Support    | 124.95 |
+----+------------------+--------+

Orders:

+----+------------+
| id |  customer  |
+----+------------+
|  1 | John Smith |
|  2 | Bob Newbie |
|  3 | Bill Jobs  |
|  4 | Ray Stantz |
+----+------------+

order_extras:

+----+----------+----------+
| id | order_id | extra_id |
+----+----------+----------+
|  1 |        4 |        2 |
|  2 |        3 |        1 |
|  3 |        3 |        3 |
|  4 |        1 |        1 |
+----+----------+----------+

Desired Output:

+----------+----------------+-----------+------------------+---------------+
| 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             |
+----------+----------------+-----------+------------------+---------------+

Can you post some sample data and the desired result? It might be easier to understand what you are doing without reading the word problem. :) — Taryn, Mar 21 '13 at 21:13
From what I Can tell, MySQL doesn't support any sort of pivot keyword or "true" pivoting at all. Take a look at this post. This guy wrote a crazy sproc to handle it: http://stackoverflow.com/questions/6158230/mysql-pivot-table-with-dynamic-headers-based-on-single-column-data — Adam Plocher, Mar 21 '13 at 21:21
Just updated the first post with some examples of what I'm hoping to do — Sk446, Mar 21 '13 at 21:34
There is a question like yours [here](http://stackoverflow.com/questions/10925445/mysql-select-dynamic-row-values-as-column-names-another-column-as-value). Hope it helps. — Felypp Oliveira, Mar 21 '13 at 21:41

score 2 · Accepted Answer · answered Mar 21 '13 at 22:06

Unfortunately, MySQL does not have a pivot function, but is can be replicated using an aggregate function with a CASE expression.

If you have a known number of extras, then you can hard-code the query:

select o.id,
  o.customer,
  max(case when e.name = 'Insurance' then e.price else 0 end) Insurance,
  max(case when e.name = 'Lifetime Updates' then e.price else 0 end) `Lifetime Updates`,
  max(case when e.name = 'Phone Support' then e.price else 0 end) `Phone Support`
from orders o
left join order_extras oe
  on o.id = oe.order_id
left join extras e
  on oe.extra_id = e.id
group by o.id, o.customer

See SQL Fiddle with Demo.

For your situation, it seems like you will have an unknown number of values. If that is the case, then you will need to use a prepared statement to generate dynamic sql:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN e.name = ''',
      name,
      ''' THEN e.price else 0 END) AS `',
      name, '`'
    )
  ) INTO @sql
FROM extras;

SET @sql 
  = CONCAT('SELECT o.id,
                o.customer, ', @sql, ' 
            from orders o
            left join order_extras oe
              on o.id = oe.order_id
            left join extras e
              on oe.extra_id = e.id
            group by o.id, o.customer');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo.

Both of these versions will give the result:

| ID |   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 |

Firstly thanks for that second example - thats spot on! I've tried running it via my local database, and the query runs successfully but doesn't actually return anything. Bit odd, its obviously working as its showing a row count, but there just aren't any results listed. Tried this in both phpMyAdmin and the mac app 'Sequel Pro' :/ — Sk446, Mar 22 '13 at 10:41
It's strange - if I run it as a standard query it doesn't output anything. However thankfully I wanted to export it to CSV anyway. Using outfile, it works perfectly! — Sk446, Mar 22 '13 at 15:08

echo_Me · Answer 2 · 2013-03-21T22:12:08.463

try this .

    select o.id , o.customer ,
    max(if(e.name = 'Insurance' , round(e.price,2), 0)) as Insurance,
    max(if(e.name = 'Lifetime Updates' , round(e.price,2), 0)) as Lifetime_Updates,
    max(if(e.name = 'Phone Support' , round(e.price,2), 0)) as Phone_Support

     from orders o
     left join order_extras oe
     on o.id = oe.order_id
    left join Extras e
    on oe.extra_id = e.id

    group by o.id, o.customer

DEMO HERE

OUTPUT IS:

   ID   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

MySQL column names as field names for a many to many relationship with a junction table

2 Answers2