Search code examples
mysqlsqlcase

Explode one line to multiple in MySQL

I have a table looks like this:

product    price99    price100
A          2          1
B          3          2
..

I don't know how to do that in MySQL to explode that into a format like this, like using the melt and cast function in R.

product     quantity_min    quantity_max    price
A           1               99              2
A           100             999999          1
B           1               99              3
B           100             999999          2
..

I have a feeling that it might need case statement? but really having a hard time making it work. What can I try next?

Solution

  • I would do this like

    select 
    product, 
    1 as 'quantity_min', 
    99 as 'quantity_max', 
    price99 as 'price'
FROM Table1
UNION ALL
select 
    product, 
    100 as 'quantity_min', 
    999999 as 'quantity_max', 
    price100 as 'price'
FROM Table1