CODEWITHSUNDEEP
sqloracle-database
Arzu Isakova
Arzu Isakova

Reputation: 13

show rows by quantity of orders

I have following table

NAME    quantity
a           5
b           3
c           2

I need write some oracle sql (only) query which will output following:

NAME    quantity
a           1
a           1
a           1
a           1
a           1
b           1
b           1
b           1
c           1
c           1

Upvotes: 0

Views: 45

Answers (2)

Adam
Adam

Reputation: 5599

Well, I'm not an Oracle guy, but I found that question interesting. I'm rather in PostgreSQL. If you know an Oracle's equivalent of PostgreSQL's generate_series function, the solution could look like this:

SELECT
    X.name,
    1
FROM (
    SELECT
        T.name,
        generate_series(1, T.quantity)
    FROM
        unnest(ARRAY[('a'::char, 5), ('b'::char, 3), ('c'::char, 2)]) AS T(name char(1), quantity integer)
) AS X;

The unnest part is just to emulate the original data table.

Maybe this could help you find an answer.

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21075

with  
row_num as (select rownum i from dual connect by level <= (select max(quantity) quantity from tab))
select NAME, QUANTITY from tab join row_num on row_num.i <= tab.quantity
order by 1

The CTO query provides the grid (rows 1 to max quantity). Use it to join to your table constraining the quantity.

Upvotes: 1

Related Questions