I have a scenario like below (Oracle's SQL).

Table:Employee

Table: Role_master

With the above tables, i would like to join both and expecting the output like below. Please help on the better way.

CodePudding user response：

Here's one option:

• sample data in lines #1 - 12; query begins at line #14
• split employee.role into rows (i.e. separate values) so that you could join them to role_master.role
• aggregate them back (using listagg)

SQL> with
  2  employee (sno, name, role) as
  3    (select 1, 'a', 'ELE,PLU,OTH' from dual union all
  4     select 2, 'b', 'MAN,DIR'     from dual
  5    ),
  6  role_master (role, role_name) as
  7    (select 'ELE', 'Electrician' from dual union all
  8     select 'PLU', 'Plumber'     from dual union all
  9     select 'OTH', 'Other'       from dual union all
 10     select 'MAN', 'Manager'     from dual union all
 11     select 'DIR', 'Director'    from dual
 12    )
 13  --
 14  select e.sno,
 15         e.name,
 16         listagg(m.role_name, ',') within group (order by column_value) role
 17  from employee e cross join
 18    table(cast(multiset(select level from dual
 19                        connect by level <= regexp_count(e.role, ',')   1
 20                       ) as sys.odcinumberlist))
 21    join role_master m on m.role = regexp_substr(e.role, '[^,] ', 1, column_value)
 22  group by e.sno, e.name;

       SNO NAME ROLE
---------- ---- ----------------------------------------
         1 a    Electrician,Plumber,Other
         2 b    Manager,Director

SQL>

CodePudding user response：

Another solution could use this logic :

1. First, generate the maximum number of rows needed for the split step (required_rows_v)
2. Then, make left join between the three data sources like below
3. Then, use listagg function to re-aggregate rows

With required_rows_v (lvl) as (
select level lvl
from dual
connect by level <= ( select max( regexp_count( e.Role, '[^,] ' )  )  from employee e )
)
select e.SNO,
      e.NAME,
    listagg(rm.Role_name, ',')within group (order by e.SNo, v.lvl) Role
from employee e
left join required_rows_v v on v.lvl <= regexp_count( e.Role, '[^,] ' )
left join Role_master rm on rm.Role = regexp_substr( e.Role, '[^,] ', 1, v.lvl )
group by e.SNO, e.NAME

demo