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

Question

I'm using oracle to output line items in from a shopping app. Each item has a quantity field that may be greater than 1 and if it is, I'd like to return that row N times.

Here's what I'm talking about for a table

product_id, quanity
1, 3,
2, 5

And I'm looking a query that would return

1,3
1,3
1,3
2,5
2,5
2,5
2,5
2,5

Is this possible? I saw this answer for SQL Server 2005 and I'm looking for almost the exact thing in oracle. Building a dedicated numbers table is unfortunately not an option.

Solution 1

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.quantity
order by 1;

Solution 2

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.quantity
ORDER 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 SampleTypeSet
PIPELINED
IS
    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)

Comments

[Kirill Leontev]

do you use 11g? I was never able to run subquery in connect by level clause in 9i.

[Martin Mares]

you are right, on Oracle 9i raise error: ORA-01473. I'll write a better solution for Oracle 9i... wait i minute, I'll update my "answer" :)

[user126715]

thanks for your thorough reply. I wound up going with hardcoding my max level because the table I'm working against is massive with no indexing on the quantity field so it was falling over when I tested it like that. If you had indexing on that quantity field though, this is a more flexible way to calculate it.