Oracle Query - Join with comma separated data

Question

Table Name : crm_mrdetails

 id | mr_name | me_email     | mr_mobile  | mr_doctor|
 -----------------------------------------------------
 1  | John    |[email protected] | 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    |[email protected] |cordiologist,Physician,Nurse|

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

Answer 1

First of all we must acknowledge that is a bad data model. The column mr_doctor violates First Normal Form. This is not some abstruse theoretical point. Not being in 1NF means we must write more code to lookup the meaning of the keys instead of using standard SQL join syntax. It also means we cannot depend on the column containing valid IDs: mr_doctor can contain any old nonsense and we must write a query which will can handle that. See Is storing a delimited list in a database column really that bad? for more on this.

Anyway. Here is a solution which uses regular expressions to split the mr_doctor column into IDs and then joins them to the mr_doctor table. The specialization column is concatenated to produce the required output.

select mrdet.id, 
       mrdet.mr_name,
       mrdet.me_email,
       listagg(mrdoc.specialization, ',') 
                     within group (order by mrdoc.specialization) as doctor_specialization
from mr_details mrdet
     join (
        select distinct id, 
               regexp_substr(mr_doctor, '(,?)([0-9]+)(,?)', 1, level, null, 2) as dr_id
        from mr_details 
        connect by level <= regexp_count(mr_doctor, '(,?)([0-9]+)')
       ) as mrids
    on mrids.id = mrdet.id
    left outer join mr_doctor mrdoc
       on mrids.dr_id = mr_doc.id
group by mrdet.id, 
       mrdet.mr_name,
       mrdet.me_email
/

This solution is reasonably resilient despite the data model being brittle. It will return results if the string has too many commas, or spaces. It will ignore values which are letters or otherwise aren't numbers. It won't hurl if the extracted number doesn't match an ID in the mr_doctor table. Obviously the results are untrustworthy for those reasons, but that's part of the price of a shonky data model.

Can you please explain the following: (,?)([0-9]+)(,?)

The pattern matches zero or one comma followed by one or more digits followed by zero or one comma. Perhaps the (,?) in the matched patterns aren't strictly necessary. However, without them, this string 2 3 4 would match the same three IDs as this string 2,3,4. Maybe that's correct maybe it isn't. When the foreign keys are stored in a CSV column instead of being enforced through a proper constraint what does 'correct' even mean?

Answer 2

You have to split data in mr_doctor column into rows, join table crm_mrdoctor and then use listagg(). How to split data? Splitting string into multiple rows in Oracle

select t.id, max(mr_name) mr_name, 
       listagg(specialization, ', ') within group (order by rn) specs
  from (
    select id, mr_name, levels.column_value rn, 
           trim(regexp_substr(mr_doctor, '[^,]+', 1, levels.column_value)) as did
      from crm_mrdetails t,
           table(cast(multiset(select level 
                                 from dual 
                                 connect by level <= 
                                     length(regexp_replace(t.mr_doctor, '[^,]+')) + 1) 
                      as sys.odcinumberlist)) levels) t
  left join crm_mr_doctor d on t.did = d.id
  group by t.id

Demo and result:

with 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),
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
    )
select t.id, max(mr_name) mr_name, 
       listagg(specialization, ', ') within group (order by rn) specs
  from (
    select id, mr_name, levels.column_value rn, 
           trim(regexp_substr(mr_doctor, '[^,]+', 1, levels.column_value)) as did
      from crm_mrdetails t,
           table(cast(multiset(select level 
                                 from dual 
                                 connect by level <= 
                                     length(regexp_replace(t.mr_doctor, '[^,]+')) + 1) 
                      as sys.odcinumberlist)) levels) t
  left join crm_mr_doctor d on t.did = d.id
  group by t.id

Output:

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