Convert two rows single column

 

 Expected Output:

 

 

 

drop table t;

create table t(c varchar2(10));

 

insert into t values('A,B');

insert into t values('K,L,M,N');

COMMIT;

 

select * from t;

 

select max(regexp_count(c,',')+1) from t;

 

select * from dual connect by level <=(select max(regexp_count(c,',')+1) from t);

 

select level from dual connect by level <=(select max(regexp_count(c,',')+1) from t);

 

select * from t,

(select level l from dual connect by level <=(select max(regexp_count(c,',')+1) from t))

order by c,l ;

 

select * from t,

(select level l from dual connect by level <=(select max(regexp_count(c,',')+1) from t))

where l<=regexp_count(c,',')+1

order by c,l ;

 

select c,l,regexp_substr(c,'\w',1,l) from t,

(select level l from dual connect by level <=(select max(regexp_count(c,',')+1) from t))

where l<=regexp_count(c,',')+1

order by c,l ;

 

select regexp_substr(c,'\w',1,l) from t,

(select level l from dual connect by level <=(select max(regexp_count(c,',')+1) from t))

where l<=regexp_count(c,',')+1

order by c,l ;

----------------------------------------------------------------------------------------------------

select c from t,

lateral(select level l from dual connect by level<=regexp_count(c,',')+1);

 

select c,l,regexp_substr(c,'\w',1,l) from t,

lateral(select level l from dual connect by level<=regexp_count(c,',')+1);

 

select regexp_substr(c,'\w',1,l) from t,

lateral(select level l from dual connect by level<=regexp_count(c,',')+1);