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 continent (cid, continent) AS
SELECT 1, 'Africa' FROM DUAL UNION ALL
SELECT 2, 'Asia' FROM DUAL UNION ALL
SELECT 3, 'South America' FROM DUAL;
3 rows affected
CREATE TABLE countries (id, country_name, is_preferred, cities) AS
SELECT 1, 'Dominican Republic', 'Y', 'Kinshasa' FROM DUAL UNION ALL
SELECT 1, 'Nigeria', 'Y', 'Lagos' FROM DUAL UNION ALL
SELECT 1, 'Egypt', 'Y', 'Cairo' FROM DUAL UNION ALL
SELECT 2, 'China', 'N', 'Shanghai' FROM DUAL UNION ALL
SELECT 2, 'Pakistan', 'Y', 'Karachi' FROM DUAL UNION ALL
SELECT 3, 'Brazil', 'Y', 'Sao Paulo' FROM DUAL UNION ALL
SELECT 3, 'Peru', 'Y', 'Lima' FROM DUAL;
7 rows affected
SELECT c.continent,
LISTAGG(ct.cities, ',') WITHIN GROUP (ORDER BY cities) AS cities
FROM continent c
INNER JOIN countries ct
ON c.cid = ct.id
WHERE ct.is_preferred = 'Y'
GROUP BY
c.continent
HAVING COUNT(1) > 1
ORDER BY
c.continent;
| CONTINENT | CITIES |
|---|---|
| Africa | Cairo,Kinshasa,Lagos |
| South America | Lima,Sao Paulo |