CREATE TABLE CATEGORIES
(
ID NUMBER,
NAME VARCHAR2(40)
)
/
INSERT INTO CATEGORIES VALUES(1,'蕎麦')
/
INSERT INTO CATEGORIES VALUES(2,'うどん')
/
INSERT INTO CATEGORIES VALUES(3,'イタリアン')
/
INSERT INTO CATEGORIES VALUES(4,'ピザ')
/
INSERT INTO CATEGORIES VALUES(5,'ワイン')
/
INSERT INTO CATEGORIES VALUES(6,'カフェ')
/
INSERT INTO CATEGORIES VALUES(7,'ラーメン')
/
INSERT INTO CATEGORIES VALUES(8,'ファストフード')
/
CREATE TABLE LOCATION_CATEGORIES
(
LOCATION_ID NUMBER,
CATEGORY_ID NUMBER
)
/
INSERT INTO LOCATION_CATEGORIES VALUES(2,1)
/
INSERT INTO LOCATION_CATEGORIES VALUES(2,2)
/
INSERT INTO LOCATION_CATEGORIES VALUES(3,3)
/
INSERT INTO LOCATION_CATEGORIES VALUES(3,4)
/
INSERT INTO LOCATION_CATEGORIES VALUES(3,5)
/
INSERT INTO LOCATION_CATEGORIES VALUES(4,6)
/
INSERT INTO LOCATION_CATEGORIES VALUES(5,7)
/
INSERT INTO LOCATION_CATEGORIES VALUES(6,8)
/
SELECT
LOCATION_CATEGORIES.LOCATION_ID
, LISTAGG(CATEGORIES.NAME, ',') WITHIN
GROUP(ORDER BY CATEGORIES.ID) CATEGORY_NAMES
FROM
LOCATION_CATEGORIES
INNER JOIN
CATEGORIES
ON CATEGORIES.ID = LOCATION_CATEGORIES.CATEGORY_ID
GROUP BY
LOCATION_CATEGORIES.LOCATION_ID
