How can I return multiple identical rows based on a quantity field in the row itself?

I've used 15 as a maximum for the example, but you should set it to 9999 or whatever the maximum quantity you will support.

create table t (product_id number, quantity number);insert into t values (1,3);insert into t values (2,5);select t.*   from t     join (select rownum rn from dual connect by level < 15) a                                  on a.rn <= t.quantityorder by 1;

First create sample data:

create table my_table (product_id number , quantity number);insert into my_table(product_id, quantity) values(1,3);insert into my_table(product_id, quantity) values(2,5);

And now run this SQL:

  SELECT product_id, quantity    FROM my_table tproducts        ,(    SELECT LEVEL AS lvl                FROM dual          CONNECT BY LEVEL <=  (SELECT MAX(quantity) FROM my_table)) tbl_sub   WHERE tbl_sub.lvl BETWEEN 1 AND tproducts.quantityORDER BY product_id, lvl;PRODUCT_ID   QUANTITY---------- ----------         1          3         1          3         1          3         2          5         2          5         2          5         2          5         2          5

This question is propably same as this: how to calc ranges in oracle

Update solution, for Oracle 9i:

You can use pipelined_function() like this:

CREATE TYPE SampleType AS OBJECT(  product_id number,  quantity varchar2(2000))/CREATE TYPE SampleTypeSet AS TABLE OF SampleType/CREATE OR REPLACE FUNCTION GET_DATA RETURN SampleTypeSetPIPELINEDIS    l_one_row SampleType := SampleType(NULL, NULL);BEGIN    FOR cur_data IN (SELECT product_id, quantity FROM my_table ORDER BY product_id) LOOP        FOR i IN 1..cur_data.quantity LOOP            l_one_row.product_id := cur_data.product_id;            l_one_row.quantity := cur_data.quantity;            PIPE ROW(l_one_row);        END LOOP;    END LOOP;    RETURN;END GET_DATA;/

Now you can do this:

SELECT * FROM TABLE(GET_DATA());

Or this:

CREATE OR REPLACE VIEW VIEW_ALL_DATA AS SELECT * FROM TABLE(GET_DATA());SELECT * FROM VIEW_ALL_DATA;

Both with same results.

(Based on my article pipelined function)