CODEWITHSUNDEEP
sqldatabaseoracle-database
DieHard345
DieHard345

Reputation: 13

Populate a numbered row until reaching a specific value with another column

I have a table full of account numbers and period/terms for loan(loan term is in months)

What I need to do is populate a numbered row for each account number that is less than or equal to the loan term. I've attached a screen shot below:

Example

So for this specific example, I will need 48 numbered rows for this account number, as the term is only 48 months.

Thanks for the help!

Upvotes: 0

Views: 39

Answers (1)

user5683823
user5683823

Reputation: 

with
     test_data ( account_nmbr, term ) as (
       select 'ABC200', 6 from dual union all
       select 'DEF100', 8 from dual
     )
-- End of simulated inputs (for testing purposes only, not part of the solution).
-- SQL query begins BELOW THIS LINE.
select     level as row_nmbr, term, account_nmbr
from       test_data
connect by level <= term
       and prior account_nmbr = account_nmbr
       and prior sys_guid() is not null
order by   account_nmbr, row_nmbr   --   If needed
;

  ROW_NMBR        TERM  ACCOUNT_NMBR
  --------  ----------  ------------
         1           6  ABC200
         2           6  ABC200
         3           6  ABC200
         4           6  ABC200
         5           6  ABC200
         6           6  ABC200
         1           8  DEF100
         2           8  DEF100
         3           8  DEF100
         4           8  DEF100
         5           8  DEF100
         6           8  DEF100
         7           8  DEF100
         8           8  DEF100

In Oracle 12, you can use the LATERAL clause for the same:

with
     test_data ( account_nmbr, term ) as (
       select 'ABC200', 6 from dual union all
       select 'DEF100', 8 from dual
     )
-- End of simulated inputs (for testing purposes only, not part of the solution).
-- SQL query begins BELOW THIS LINE.
select   l.row_nmbr, t.term, t.account_nmbr
from     test_data t,
         lateral (select level as row_nmbr from dual connect by level <= term) l
order by account_nmbr, row_nmbr   --  If needed
;

Upvotes: 1

Related Questions