Elm Elm Elm Elm -4 years ago 59
MySQL Question

Multiple select from one row from A table and insert result as multiple rows in B table and repeat in one query

I need insert data from static table to table with multiple custom options, so it looks like below.

A table:

+------+------+--------+-------+
| name | type | weight | color |
+------+------+--------+-------+
| 1 | A | 10 | green |
+------+------+--------+-------+
| 2 | B | 3 | blue |
+------+------+--------+-------+
| 3 | D | 9 | gold |
+------+------+--------+-------+


Desired Output:

+------+-------------+--------------+
| name | option_name | option_value |
+------+-------------+--------------+
| 1 | type | A |
+------+-------------+--------------+
| 1 | weight | 10 |
+------+-------------+--------------+
| 1 | color | green |
+------+-------------+--------------+
| 2 | type | B |
+------+-------------+--------------+
| 2 | weight | 3 |
+------+-------------+--------------+
| 2 | color | blue |
+------+-------------+--------------+
| 3 | type | D |
+------+-------------+--------------+
| 3 | weight | 9 |
+------+-------------+--------------+
| 3 | color | gold |
+------+-------------+--------------+


Is it possible?

Answer Source

You can use cross join trick to "UNPIVOT" the values:

select 
    t.name,
    case x.i 
        when 1 then 'type'
        when 2 then 'weight'
        when 3 then 'color'
    end option_name,
    case x.i
        when 1 then type
        when 2 then cast(weight as char(50))
        when 3 then color
    end option_value
from your_table t
cross join (
    select 1 i union all
    select 2 i union all
    select 3 i
) x

cast(weight as char(50)) is required for weight because the data types need to be consistent and weight is (probably) a numeric column and needs to be converted into string.

SQLFiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download