Oracle, typical SQL tasks. The multiplication of the rows of the table depending on the value of the number in the column

On the nose of the winter, frost is approaching, which means that today we will pickle bananas. For this we need the following ingredients:

ID	INGREDIENT	MEASURE	QUANTITY
one	Banana	Thing	3
2	Parsley	Branch	2
3	Water	Liter	3
four	Salt	Spoon	one
five	Vinegar	Spoon	2

It is necessary to obtain a data set showing the ingredients in the bank individually, taking into account their quantity:

INGREDIENT	MEASURE	QUANTITY
Banana	Thing	one
Banana	Thing	one
Banana	Thing	one
Parsley	Branch	one
Parsley	Branch	one
Water	Liter	one
Water	Liter	one
Water	Liter	one
Salt	Spoon	one
Vinegar	Spoon	one
Vinegar	Spoon	one

In essence, you need to perform an operation inverse to grouping and aggregation using the count () function.

To start, how should boil a jar:
')

create table bottle as with t (id, ingredient, measure, quantity) as ( select 1, '', '', 3 from dual union all select 2, '', '', 2 from dual union all select 3, '', '', 3 from dual union all select 4, '', '', 1 from dual union all select 5, '', '', 2 from dual ) select * from t; alter table bottle add primary key (id); 

And now directly cooking recipes.

Method 1

Probably not the fastest, especially with a very large number of lines:

 select b.ingredient, b.measure, 1 quantity from bottle b, ( select level lvl from dual connect by level <= (select max(quantity) from bottle)) x where b.quantity >= x.lvl order by b.id 

Method 2

Among housewives there is an opinion that it is possible to create a stationary table with a large number of rows and a unique key:

 create table multiplier_rows as select rownum as row_num from dual connect by level <= 10000; alter table multiplier_rows add primary key (row_num); 

And use it collectively in the same way as subquery X from the first method:

 select b.ingredient, b.measure, 1 quantity from bottle b, multiplier_rows x where b.quantity >= x.row_num order by b.id 

Whether this method is more effective is a moot point.

Note: hereinafter, query plans are not given, as well as options for their use on different data volumes and values, but such tests are welcome in the comments.

Method 3

Through recursive query. Leading Banana Studies recommend:

 select ingredient, measure, 1 quantity from bottle connect by prior id = id and prior dbms_random.value is not null and level <= quantity order by id 

Method 4

In continuation of the topic - I made a recursion, but already through WITH + UNION ALL, this is what happened:

 with boo (id, i, m, q) as ( select id, ingredient, measure, quantity from bottle union all select id, i, m, q-1 from boo where q > 1 ) select i ingredient, m measure, 1 quantity from boo order by id 

Method 5

Through the collection. For those, of course, who can cook them:

 select b.ingredient, b.measure, 1 quantity from bottle b, table(cast(multiset(select null from dual connect by level <= b.quantity) as sys.odcinumberlist)) x order by b.id 

Method 6

 select b.ingredient, b.measure, 1 quantity from bottle b, table (select cast(collect(1) as sys.odcinumberlist) from dual connect by level <= b.quantity) x order by b.id 

Method 7

And finally, a compliment from the chef:

 select ingredient, measure, 1 quantity from bottle model partition by (id, ingredient, measure, quantity) dimension by (0 d) measures(0 m) rules iterate (10000) until m[iteration_number] = iteration_number ( m[iteration_number] = cv(quantity) - 1 ) order by id 

Modelka with bananas - this is me purely for aesthetics.



UPD: Method 8

For Oracle 12c, for comments xtender - a gift from the institution.

 select b.ingredient, b.measure, 1 quantity from bottle b, lateral(select null from dual connect by level <= b.quantity) x order by b.id 

That's all, it remains only to tighten the jar and, together with all the contents, throw it into the trash can:

 drop table bottle; drop table multiplier_rows; 

Fine! Now pickled bananas are fully prepared.

I would appreciate:

• new recipes
• improvement available
• choosing the best recipe in terms of performance first

In the meantime, until we meet again.