ZyTPdAqm

1SELECT REMIT_TO.ID
2 , LISTAGG(EMAIL, ';') WITHIN GROUP(ORDER BY REMIT_TO.ID) REMIT_TO.EFT_EMAIL_ADDR
3
4 FROM (SELECT REMIT_TO.ID 
5        , regexp_substr(REMIT_TO.EFT_EMAIL_ADDR, '[^;]+', 1, RN) email
6
7    FROM IQMS.REMIT_TO
8    CROSS JOIN (SELECT ROWNUM RN
9                   FROM(SELECT MAX (REGEXP_COUNT(REMIT_TO.EFT_EMAIL_ADDR, '[^;]+')) ML
10                          FROM IQMS.REMIT_TO
11                        )
12                       CONNECT BY LEVEL <= ML
13                  )
14     )                             
15 WHERE EMAIL LIKE '%@gmail.com%'
16 GROUP BY REMIT_TO.ID
17	
18SELECT REMIT_TO.ID
19 , LISTAGG(EMAIL, ';') WITHIN GROUP(ORDER BY REMIT_TO.ID) REMIT_TO.EFT_EMAIL_ADDR
20FROM 
21(
22  SELECT REMIT_TO.ID 
23        , regexp_substr(REMIT_TO.EFT_EMAIL_ADDR, '[^;]+', 1, RN) email
24  FROM IQMS.REMIT_TO REMIT_TO
25  CROSS JOIN 
26  (
27    SELECT ROWNUM RN
28    FROM
29    (
30      SELECT MAX (REGEXP_COUNT(REMIT_TO.EFT_EMAIL_ADDR, '[^;]+')) ML
31      FROM IQMS.REMIT_TO
32    ) x2 -- alias needed
33    CONNECT BY LEVEL <= ML
34  ) x1   -- alias needed  
35) REMIT_TO   -- alias needed                         
36WHERE EMAIL LIKE '%@gmail.com%'
37GROUP BY REMIT_TO.ID
38	
39SELECT REMIT_TO.ID
40 , LISTAGG(EMAIL, ';') WITHIN GROUP(ORDER BY REMIT_TO.ID)
41 , REMIT_TO.EFT_EMAIL_ADDR...