SQLize Online / PHPize Online  /  SQLtest Online

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, STRING_AGG(CASE WHEN AZ.Area LIKE 'PS_%' THEN AZ.Area END, ',') Temp, STRING_AGG(CASE WHEN AZ.Area LIKE 'VPA%' THEN AZ.Area END, ',') Voice, STRING_AGG(CASE WHEN AZ.Area NOT LIKE 'PS_%' AND AZ.Area NOT LIKE 'VPA%' THEN AZ.Area END, ',') Other FROM Area_Zone AZ JOIN Zone Z On Z.Zone = AZ.Zone GROUP BY Z.Zone