Oracle Query - Join with comma separated data

user_4191220 0
Viewed 6672

Table Name : crm_mrdetails

 id | mr_name | me_email     | mr_mobile  | mr_doctor|
 -----------------------------------------------------
 1  | John    |abc@gmail.com | 1234555555 | ,1,2,3   |

Table Name : crm_mr_doctor

id | dr_name     | specialization|  
----------------------------------  
1  | Abhishek    | cordiologist  |
2  | Krishnan    | Physician     |
3  | Krishnan    | Nurse         |

The concatenated values in mrdetails.mr_doctor are the foreign keys for mr_doctor.id. I need to join on them to produce output like this:

 id | mr_name | me_email     |Doctor_Specialization|
 -------------------------------------------------
 1  | John    |abc@gmail.com |cordiologist,Physician,Nurse|

I'm new to Oracle, I'm using Oracle 12C. Any help much appreciated.

oracle12c oracle string
6 Answers

You can use a recursive sub-query and simple string functions (which may be faster than using regular expressions and a correlated hierarchical query):

Oracle Setup:

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;

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;

Query:

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

Output:

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

db<>fiddle here

user_15092640

Please go through https://oracle-base.com/articles/misc/string-aggregation-techniques String Aggregation Techniques

or

SELECT deptno, LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees FROM (SELECT deptno, ename, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev FROM emp) GROUP BY deptno CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno START WITH curr = 1

or

listagg and wm_concat an also be used as other people have used it

user_119428550

How about this one? I have not tested it, so there could be any syntax error.

select id,mr_name,me_email,listagg(specialization,',') within group (order by specialization) as Doctor_Specialization
from
(select dtls.id,dtls.mr_name,dtls.me_email,dr.specialization
from crm_mrdetails dtls,
crm_mr_doctor dr
where INSTR(','||dtls.mr_doctor||',' , ','||dr.id||',') > 0
) group by id,mr_name,me_email;
user_45802800
Related