Creating numbered row based on data in another column [ORACLE]

user_9633357 0
Viewed 30

I have a requirement to create a specific 'ID' for a number of line items based on the text from one column and a number from another column. I essentially need to duplicate a row 'n' number of times based on the number in a column, and each duplicate row have an incrementing number as an ID.

The original data would look something like this:

PN    QTY
===   ===
ABC   1
DEF   3
GHI   1

I require a query to return a table that looks like the following:

ID       PN    QTY
===      ===   ===
ABC;1    ABC   1
DEF;1    DEF   3
DEF;2    DEF   3
DEF;3    DEF   3
GHI;1    GHI   1

I'm sure I could do this iteratively using a script after the SQL query has been run but I want to avoid this. Thanks!

recursive-query select oracle sql
1 Answers

One option uses a recursive query:

with cte (pn, qty, n) as (
    select pn, qty, 1 from mytable where qty > 0
    union all
    select pn, qty, n + 1 from cte where n < qty
)
select pn, qty from cte order by pn

Demo on DB Fiddle:

PN  | QTY
:-- | --:
ABC |   1
DEF |   3
DEF |   3
DEF |   3
GHI |   1
user_106767160
Related