2

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.

2
  • 1
    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
4

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;
5
  • 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.
    – Hermanto
    Nov 9 '16 at 23:33
  • I've edited my answer.
    – Hermanto
    Nov 9 '16 at 23:47
1
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.