By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1
("DEPARTMENT_ID_FK" varchar2(3), "EMAIL_ADDRESS" varchar2(13));
INSERT ALL
INTO Table1 ("DEPARTMENT_ID_FK", "EMAIL_ADDRESS")
VALUES ('001', 'a@example.com')
INTO Table1 ("DEPARTMENT_ID_FK", "EMAIL_ADDRESS")
VALUES ('002', 'b@example.com')
INTO Table1 ("DEPARTMENT_ID_FK", "EMAIL_ADDRESS")
VALUES ('001', 'c@example.com')
SELECT * FROM dual;
3 rows affected
-- Oracle 11gR2
SELECT DEPARTMENT_ID_FK, LISTAGG(EMAIL_ADDRESS, ',') WITHIN GROUP (ORDER BY EMAIL_ADDRESS) AS EMAIL_ADDRESS
FROM table1
GROUP BY DEPARTMENT_ID_FK
| DEPARTMENT_ID_FK | EMAIL_ADDRESS |
|---|---|
| 001 | a@example.com,c@example.com |
| 002 | b@example.com |
-- Oracle 10
SELECT DEPARTMENT_ID_FK,
RTRIM(XMLAGG(XMLELEMENT(e, EMAIL_ADDRESS, ',')
ORDER BY EMAIL_ADDRESS).EXTRACT('//text()').GETCLOBVAL(), ',') AS EMAIL_ADDRESS
FROM table1
GROUP BY DEPARTMENT_ID_FK
| DEPARTMENT_ID_FK | EMAIL_ADDRESS |
|---|---|
| 001 | a@example.com,c@example.com |
| 002 | b@example.com |