CREATE TABLE xx_report_email (
    report_name   VARCHAR2(20),
    email         VARCHAR2(100)
);

INSERT INTO xx_report_email VALUES (
    'rep1',
    'abc@gmail.com;xyz@gmail.com'
);

INSERT INTO xx_report_email VALUES (
    'rep2',
    'def@gmail.com;xyz@gmail.com'
);

INSERT INTO xx_report_email VALUES (
    'rep3',
    'def@gmail.com;abc@gmail.com'
);
            

email           reports
abc@gmail.com   rep1,rep3
xyz@gmail.com   rep1,rep2
def@gmail.com   rep2,rep3

SQL> @v

Oracle version: 10.2.0.5.14 EE

SQL> col email format a30
SQL> col reports format a30
SQL> with
  2    data as (select report_name, ';'||email||';' email from xx_report_email),
  3    split as (
  4      select report_name,
  5             substr(email,
  6                    instr(email, ';', 1, column_value)+1,
  7                    instr(email, ';', 1, column_value+1)-instr(email, ';', 1, column_value)-1
  8                   ) email
  9       from data,
 10            table(cast(multiset(select level from dual
 11                                connect by level < length(email)-length(replace(email,';')))
 12                  as sys.odciNumberList))
 13    )
 14  select email, substr(sys_connect_by_path(report_name,','),2) reports
 15  from split a
 16  where connect_by_isleaf = 1
 17  connect by prior email = email and prior report_name < report_name
 18  start with report_name = (select min(report_name) from split b where b.email = a.email)
 19  order by email
 20  /
EMAIL                          REPORTS
------------------------------ ------------------------------
abc@gmail.com                  rep1,rep3
def@gmail.com                  rep2,rep3
xyz@gmail.com                  rep1,rep2

SQL> with
  2    data as (select report_name, ';'||email||';' email from xx_report_email),
  3    split as (
  4      select report_name,
  5             substr(email,
  6                    instr(email, ';', 1, column_value)+1,
  7                    instr(email, ';', 1, column_value+1)-instr(email, ';', 1, column_value)-1
  8                   ) email
  9       from data,
 10            table(cast(multiset(select level from dual
 11                                connect by level < length(email)-length(replace(email,';')))
 12                  as sys.odciNumberList))
 13    )
 14  select email, stragg(report_name) reports
 15  from split
 16  group by email
 17  order by email
 18  /
EMAIL                          REPORTS
------------------------------ ------------------------------
abc@gmail.com                  rep1,rep3
def@gmail.com                  rep2,rep3
xyz@gmail.com                  rep1,rep2

SQL> with
  2    data as (select report_name, ';'||email||';' email from xx_report_email),
  3    split as (
  4      select report_name,
  5             substr(email,
  6                    instr(email, ';', 1, column_value)+1,
  7                    instr(email, ';', 1, column_value+1)-instr(email, ';', 1, column_value)-1
  8                   ) email
  9       from data,
 10            table(cast(multiset(select level from dual
 11                                connect by level < length(email)-length(replace(email,';')))
 12                  as sys.odciNumberList))
 13    )
 14  select email, WM_CONCAT(report_name) reports
 15  from split
 16  group by email
 17  order by email
 18  /
EMAIL                          REPORTS
------------------------------ ------------------------------
abc@gmail.com                  rep1,rep3
def@gmail.com                  rep2,rep3
xyz@gmail.com                  rep1,rep2