Return multiple results depending on the Quantity field

Question

I have the following table of entries:

Product | Colour | Quantity --------------------------------------- Product A | Blue | 1 Product A | Red | 2 Product B | Green | 1 

How can I write a query to return the following ?:

 Product | Colour --------------------------- Product A | Blue Product A | Red Product A | Red Product B | Green 

Answer 1

 WITH numbers (rn) AS ( SELECT MAX(quantity) FROM product UNION ALL SELECT rn - 1 FROM numbers WHERE rn > 1 ) SELECT p.* FROM product p JOIN numbers n ON p.quantity >= n.rn