how to return multiple records from single record in Oracle-CodePudding

I have source Tbl like

CID   No_Of_Seats_Booked  Seat_Numbers
-------------------------------------
1            3              01A01B01C

Tgt table O/P

CID Seat_id
------------
1    01A    
1    01B    
1    01C

CodePudding user response：

Here's one option:

SQL> with test (cid, no_of_seats_booked, seat_numbers) as
  2    -- sample data
  3    (select 1, 3, '01A01B01C' from dual union all
  4     select 2, 2, '02A02B'    from dual)
  5  -- query you need begins here
  6  select cid,
  7         substr(seat_numbers, 1   (column_value - 1) * 3, 3) seat_id
  8  from test cross join
  9    table(cast(multiset(select level from dual
 10                        connect by level <= no_of_seats_booked
 11                       ) as sys.odcinumberlist))
 12  order by cid, seat_id;

       CID SEA
---------- ---
         1 01A
         1 01B
         1 01C
         2 02A
         2 02B

SQL>

CodePudding user response：

Another approach, just for variety:

with demo(cid, no_of_seats_booked, seat_numbers) as
     ( select 1, 3, '01A01B01C' from dual union all
       select 2, 2, '02A02B' from dual
     )
select d.cid
     , regexp_substr(d.seat_numbers, '...', 1, r.rnum) seat_id
from   demo d
       cross apply (select rownum as rnum from dual connect by rownum <= d.no_of_seats_booked) r
order  by d.cid, seat_id;