Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE crm_mrdetails (id, mr_name, mr_doctor) as
    select 1, 'John', ',1,2,3'   from dual union all
    select 2, 'Anne', ',4,2,6,5' from dual union all
    select 3, 'Dave', ',4'       from dual;
3 rows affected

CREATE TABLE crm_mr_doctor (id, dr_name, specialization) as
    select 1, 'Abhishek', 'cordiologist' from dual union all
    select 2, 'Krishnan', 'Physician'    from dual union all
    select 3, 'Krishnan', 'Nurse'        from dual union all
    select 4, 'Krishnan', 'Onkologist'   from dual union all
    select 5, 'Krishnan', 'Surgeon'      from dual union all
    select 6, 'Krishnan', 'Nurse'        from dual;
6 rows affected

WITH crm_mrdetails_bounds ( id, mr_name, mr_doctor, start_pos, end_pos ) AS (
  SELECT id,
         mr_name,
         mr_doctor,
         2,
         INSTR( mr_doctor, ',', 2 )
  FROM   crm_mrdetails
UNION ALL
  SELECT id,
         mr_name,
         mr_doctor,
         end_pos + 1,
         INSTR( mr_doctor, ',', end_pos + 1 )
  FROM   crm_mrdetails_bounds
  WHERE  end_pos > 0
),
crm_mrdetails_specs ( id, mr_name, start_pos, specialization_id ) AS (
  SELECT id,
         mr_name,
         start_pos,
         TO_NUMBER(
           CASE end_pos
           WHEN 0
           THEN SUBSTR( mr_doctor, start_pos )
           ELSE SUBSTR( mr_doctor, start_pos, end_pos - start_pos )
           END
         )
  FROM   crm_mrdetails_bounds
)
SELECT s.id,
       MAX( s.mr_name ) AS mr_name,
       LISTAGG( d.specialization, ',' )
         WITHIN GROUP ( ORDER BY s.start_pos )
         AS doctor_specialization
FROM   crm_mrdetails_specs s
       INNER JOIN crm_mr_doctor d
       ON ( s.specialization_id = d.id )
GROUP BY s.id


ID
MR_NAME
DOCTOR_SPECIALIZATION


1
John
cordiologist,Physician,Nurse

2
Anne
Onkologist,Physician,Nurse,Surgeon

3
Dave
Onkologist

ID	MR_NAME	DOCTOR_SPECIALIZATION
1	John	cordiologist,Physician,Nurse
2	Anne	Onkologist,Physician,Nurse,Surgeon
3	Dave	Onkologist