SQLize | PHPize | SQLtest

CREATE TABLE Area_Zone ( Area varchar(64), Zone varchar(64)); INSERT INTO Area_Zone (Area, Zone) VALUES ('A', '1z'), ('B', '2z'), ('C', '3z'), ('A', '4z'), ('D', '4z'), ('PS_1', '1z'), ('PS_1', '2Z'), ('PS_2', '3Z'), ('PS_2', '4Z'), ('VPA01', '1z'), ('VPA01', '2Z'), ('VPA02', '3Z'), ('VPA02', '4Z'); CREATE TABLE Zone (Zone varchar(64)); INSERT INTO Zone (Zone) VALUES ('1z'), ('2z'), ('3z'), ('4z'); SELECT z.Zone , a1.Area as Temp , a2.Area as Voice , STRING_AGG(a3.Area,', ') WITHIN GROUP (ORDER BY a3.Area) as Other_area from Zone z left join (select * from Area_Zone where area like '%PS_%') a1 on z.zone = a1.zone left join (select * from Area_Zone where area like '%VPA%') a2 on z.zone = a2.zone left join (select * from Area_Zone where area not like '%VPA%' and area not like '%PS_%') a3 on z.zone = a3.zone Group by z.Zone, a1.Area, a2.Area