Write a query to display comma seperated

 Input:

 

Expected Output: 

create table t_val(no number,

                   name varchar2(20));

insert into t_val values(1,'jones,clark,blake');

insert into t_val values(2,'smith,adams');

insert into t_val values(3,'adams');

commit;

select no,name,regexp_count(name,',') from t_val;

1   jones,clark,blake   2

2   smith,adams         1

3   adams               0

 

select no,name,regexp_count(name,',')+1 from t_val;

1   jones,clark,blake   3

2   smith,adams         2

3   adams               1

 

select no,name,regexp_count(name,',')+1 from t_val,

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

1   jones,clark,blake   3

1   jones,clark,blake   3

1   jones,clark,blake   3

2   smith,adams         2

2   smith,adams         2

3   adams               1

 

select no,name,regexp_count(name,',')+1,

regexp_substr(name,'\w+',1,l) from t_val,

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

1   jones,clark,blake   3   jones

1   jones,clark,blake   3   clark

1   jones,clark,blake   3   blake

2   smith,adams         2   smith

2   smith,adams         2   adams

3   adams               1   adams

select no,--name,regexp_count(name,',')+1,

regexp_substr(name,'\w+',1,l) from t_val,

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

1   jones

1   clark

1   blake

2   smith

2   adams

3   adams