MySQL: Select with multiple row based in quantity column

Question

I have a order table like:

product|quantity

Example:

bread|3

I need a select like:

row1- bread
row2- bread
row3- bread

I made like this:

SELECT product FROM (
     SELECT product FROM order WHERE quantity > 0 UNION ALL
     SELECT product FROM order WHERE quantity > 1 UNION ALL
     SELECT product FROM order WHERE quantity > 2 UNION ALL
     SELECT product FROM order WHERE quantity > 3
) s;

It was Working great. But they told me the max quantity was 4. Now I saw orders with 12, 32... so I have no idea the max value.

Is there a better way to make this?

Please learn to format your questions after 15 posts. – juergen d Feb 09 '15 at 11:31 — juergen d, Feb 09 '15 at 11:31

score 9 · Accepted Answer · answered Feb 09 '15 at 11:47

You need a table that generates numbers. If your orders table has enough rows, you can use a variable to generate the numbers:

select product
from orders o join
     (select (@rn := @rn + 1) as n
      from orders o cross join (select @rn := 0) vars
     ) n
     on n.n <= o.quantity;

score 1 · Answer 2 · edited May 23 '17 at 10:29

This can be done using a number table, and in case you don't have one you can use the method described in this answer to generate a series of numbers on the fly. Using that method you can run a query such as this:

-- set up test data
CREATE TABLE Table1 (product VARCHAR(20), quantity int);
insert into Table1 values ('bread',3), ('butter',5), ('milk',2);

-- set up views for number series
CREATE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE VIEW generator_256
AS SELECT ( ( hi.n * 16 ) + lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

-- and the actual query
SELECT product, t.quantity, i.n
FROM Table1 t
JOIN generator_256 i 
ON i.n BETWEEN 1 AND t.quantity
ORDER BY t.product, i.n;

Result:

| PRODUCT | QUANTITY | N |
|---------|----------|---|
|   bread |        3 | 1 |
|   bread |        3 | 2 |
|   bread |        3 | 3 |
|  butter |        5 | 1 |
|  butter |        5 | 2 |
|  butter |        5 | 3 |
|  butter |        5 | 4 |
|  butter |        5 | 5 |
|    milk |        2 | 1 |
|    milk |        2 | 2 |

Sample SQL Fiddle

MySQL: Select with multiple row based in quantity column

2 Answers2