Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE employee (id) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
3 rows affected

CREATE TABLE general_email (id, email_code, email_address) AS
SELECT 1, 'EMAIL', 'alice@example.com' FROM DUAL UNION ALL
SELECT 1, 'EMAIL', 'abbot@example.com' FROM DUAL UNION ALL
SELECT 1, 'ABCDE', 'alice_abbot@example.com' FROM DUAL UNION ALL
SELECT 2, 'EMAIL', 'beryl@example.com' FROM DUAL UNION ALL
SELECT 2, 'EMAIL', 'baron@example.com' FROM DUAL UNION ALL
SELECT 2, 'ABCDE', 'beryl_baron@example.com' FROM DUAL UNION ALL
SELECT 3, 'EMAIL', 'carol@example.com' FROM DUAL UNION ALL
SELECT 3, 'EMAIL', 'count@example.com' FROM DUAL;
8 rows affected

SELECT E.ID,
       LISTAGG(G.EMAIL_ADDRESS, ',') WITHIN GROUP (ORDER BY G.EMAIL_ADDRESS)
         AS emails
FROM   EMPLOYEE E
       INNER JOIN GENERAL_EMAIL G ON (G.ID=E.ID)
GROUP BY E.ID
ORDER BY E.ID;


ID
EMAILS


1
abbot@example.com,alice@example.com,alice_abbot@example.com

2
baron@example.com,beryl@example.com,beryl_baron@example.com

3
carol@example.com,count@example.com


SELECT E.ID,
       LISTAGG(
         CASE WHEN G.EMAIL_CODE='EMAIL' THEN G.EMAIL_ADDRESS END,
         ','
       ) WITHIN GROUP (ORDER BY G.EMAIL_ADDRESS) AS email1,
       LISTAGG(
         CASE WHEN G.EMAIL_CODE='ABCDE' THEN G.EMAIL_ADDRESS END,
         ','
       ) WITHIN GROUP (ORDER BY G.EMAIL_ADDRESS) AS email2
FROM   EMPLOYEE E
       INNER JOIN GENERAL_EMAIL G ON (G.ID=E.ID)
GROUP BY E.ID
ORDER BY E.ID;


ID
EMAIL1
EMAIL2


1
abbot@example.com,alice@example.com
alice_abbot@example.com

2
baron@example.com,beryl@example.com
beryl_baron@example.com

3
carol@example.com,count@example.com
null

ID	EMAILS
1	abbot@example.com,alice@example.com,alice_abbot@example.com
2	baron@example.com,beryl@example.com,beryl_baron@example.com
3	carol@example.com,count@example.com

ID	EMAIL1	EMAIL2
1	abbot@example.com,alice@example.com	alice_abbot@example.com
2	baron@example.com,beryl@example.com	beryl_baron@example.com
3	carol@example.com,count@example.com	null