show rows by quantity of orders

Question

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

So you want to break apart the quantities into values of 1 for every single name? — Michael Platt, Nov 01 '17 at 16:30
So generally this is a really weird thing to do. What is the purpose of this query? Is it just to print stuff in the console? If you are passing the data back to an application, the logic to separate each of the names would be in the application. So could you elaborate a bit more about the reason you want to do this? — Michael Platt, Nov 01 '17 at 16:33
I don't quite understand "self testing test." If you are just printing you can use some cursor logic probably to loop over the rows. — Michael Platt, Nov 01 '17 at 16:35
I know, there is other solutions which can be done easily using pl/sql. I also can write some function which will pipe rows over loop or table, but I need solution in pure sql! — Arzu Isakova, Nov 01 '17 at 16:37
@Michael Platt - there are in deed use cases where this query (even is sound weird) is required. Some interfaces / tools can't cope with aggregated data and you must go back to detail level. — Marmite Bomber, Nov 01 '17 at 16:41

score 1 · Accepted Answer · answered Nov 01 '17 at 16:38

1

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.

answered Nov 01 '17 at 16:38

Marmite Bomber

19,886
4
26
53

1

can you help to write this query in ms sql also? – Arzu Isakova Nov 01 '17 at 16:44
@Arzu, for equivalent of `connect by level ..` see [here](https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers) – Marmite Bomber Nov 01 '17 at 16:48

score 0 · Answer 2 · answered Nov 01 '17 at 16:42

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.

show rows by quantity of orders

2 Answers2