Skip to Main Content
  • Questions
  • Break string into rows duplicating based on repeat counts

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Nikhil.

Asked: July 21, 2020 - 2:37 pm UTC

Answered by: Chris Saxon - Last updated: July 30, 2020 - 5:11 pm UTC

Category: Database Development - Version: 12.1.0.1

Viewed 100+ times

You Asked

Hi Connor, Chris,
Can you please have a look at below table structure.

drop table acct_test;

create table acct_test
(
account_no varchar2(50 char),
country varchar2(50 char),
no_of_txn number(9),
sys_n_status varchar2(1000 char)
);

insert into acct_test values ('ACCT00001','SG',2,'ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}');

insert into acct_test values ('ACCT00002','KL',3,'ABC-NEW{3};ABC-PROCESS{2}');
commit;


For every account_no, I need to
1. Break string sys_n_status into rows e.g. ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}
ABC NEW
ABC NEW
ABC PROCESS
ABC PROCESS
ABC PROCESS
XYZ DUMMY
2. No multiply #1 rows with no_of_txn and generate IDENTIFIER_STRING per iteration like below:

ACCT_NO   COUNTRY  SOURCE  STATUS    IDENTIFIER_STRING
--------- -------  ------  -------   -----------------
ACCT00001 SG       ABC      NEW       TXN0000000001
ACCT00001 SG       ABC      NEW       TXN0000000001
ACCT00001 SG       ABC      PROCESS   TXN0000000001
ACCT00001 SG       ABC      PROCESS   TXN0000000001
ACCT00001 SG       ABC      PROCESS   TXN0000000001
ACCT00001 SG       XYZ      DUMMY     TXN0000000001

ACCT00001 SG       ABC      NEW       TXN0000000002
ACCT00001 SG       ABC      NEW       TXN0000000002
ACCT00001 SG       ABC      PROCESS   TXN0000000002
ACCT00001 SG       ABC      PROCESS   TXN0000000002
ACCT00001 SG       ABC      PROCESS   TXN0000000002
ACCT00001 SG       XYZ      DUMMY     TXN0000000002

ACCT00002 KL       ABC      NEW       TXN0000000003
ACCT00002 KL       ABC      NEW       TXN0000000003
ACCT00002 KL       ABC      NEW       TXN0000000003
ACCT00002 KL       ABC      PROCESS   TXN0000000003
ACCT00002 KL       ABC      PROCESS   TXN0000000003

ACCT00002 KL       ABC      NEW       TXN0000000004
ACCT00002 KL       ABC      NEW       TXN0000000004
ACCT00002 KL       ABC      NEW       TXN0000000004
ACCT00002 KL       ABC      PROCESS   TXN0000000004
ACCT00002 KL       ABC      PROCESS   TXN0000000004

ACCT00002 KL       ABC      NEW       TXN0000000005
ACCT00002 KL       ABC      NEW       TXN0000000005
ACCT00002 KL       ABC      NEW       TXN0000000005
ACCT00002 KL       ABC      PROCESS   TXN0000000005
ACCT00002 KL       ABC      PROCESS   TXN0000000005

Could you please help/suggest on preparing SQL for the same.

and we said...

You can use the connect by level trick to generate rows. Placing this in a lateral join enables you to repeat each row from the source table based on values from this table.

Which has this form:

  from   tab, lateral (
    select level l from dual
    connect by level <= num_rows
  )


You can repeat this many times to:

- Split the string into rows
- Duplicate each substing {N} times
- Repeat each transaction NO_OF_TXN times

Giving something like:

with vals as (
  select a.*, 
         regexp_substr ( sys_n_status, '[^;]+', 1, l ) str,
         l n
  from   acct_test a, lateral (
    select level l from dual
    connect by level <= ( length ( sys_n_status ) - length ( replace ( sys_n_status, ';' ) ) + 1 )
  )
), rws as (
  select r.*
  from   vals r, lateral (
    select * from dual
    connect by level <= regexp_substr ( str, '[0-9]+' )
  )
)
  select * 
  from   rws, lateral ( 
     select level l from dual
     connect by level <= no_of_txn
  )
  order  by account_no, l, n;
  
ACCOUNT_NO   COUNTRY   NO_OF_TXN SYS_N_STATUS                             STR              N   L   
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-NEW{2}       1   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-NEW{2}       1   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   XYZ:DUMMY{1}     3   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-NEW{2}       1   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-NEW{2}       1   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   XYZ:DUMMY{1}     3   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   3 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   3 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   3 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   3 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   3 


From there it's just a matter of splitting the values out from STR and using dense_rank to assign the transaction numbers.

and you rated our response

  (1 rating)

Reviews

It worked!!

July 30, 2020 - 4:17 pm UTC

Reviewer: A reader

Thanks for your help Chris.

Chris Saxon

Followup  

July 30, 2020 - 5:11 pm UTC

Great :)

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.