# How to repeat row based on qty field

I have a table like

``````id product qty
1  apples  3
2  oranges 1
``````

I want to get a query that will return

``````1  apples  1
1  apples  1
1  apples  1
2  oranges 1
``````

How do I accomplish that? I've seen several answers on splitting a column into rows like "a,b,c" but not on qty like this. Any help is appreciated.

• Why would you want multiple identical rows in a result set? Nov 8 '16 at 22:16
• because on a diffrent table we store them item by item and it would make it easier to compare and join. and no unfortunately i can't fix those tables :( Nov 9 '16 at 21:58

You need a table of numbers, and fill this table with sequential number as much as you want.

``````CREATE TABLE numbers (
number int
)
ENGINE = InnoDB;

INSERT INTO numbers (number) VALUES (1), (2), (3), (4), (5); -- you can add more number according your needs.
``````

And then join this table with your table.

``````SELECT p.id, p.product, 1
FROM products p
JOIN numbers n ON p.qty >= n.number;
``````

This should produce result as you want.

If you can't create table, you can simulate table with UNION.

``````SELECT p.id, p.product, 1
FROM products p
JOIN (
SELECT 1 as number UNION
SELECT 2 UNION
SELECT 3 -- you can continue this line
) n
ON p.qty >= n.number;
``````
• sorry needs to be a query solution not table solution. can't create that table Nov 9 '16 at 21:59
• This is a query. The table needs to be created once. Nov 9 '16 at 22:03
• like i said. i can't create that table Nov 9 '16 at 22:11
• It's hard to achieve without help of additional table. Perhaps someone has more brilliant idea. Nov 9 '16 at 23:33
• I've edited my answer. Nov 9 '16 at 23:47
``````SELECT p.id, p.product, 1
FROM products p
JOIN (
SELECT @row := @row + 1 as row FROM
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
(SELECT @row:=0) r
) n ON p.qty >= n.row;
``````