WITH item_list AS
(
SELECT '연필' item FROM dual
UNION ALL SELECT '지우개' FROM dual
UNION ALL SELECT '휴지통' FROM dual
UNION ALL SELECT '볼펜'   FROM dual
UNION ALL SELECT '메모'   FROM dual
UNION ALL SELECT '건전지' FROM dual
UNION ALL SELECT '시계'   FROM dual
UNION ALL SELECT '키보드' FROM dual
)
, room_list AS
(
SELECT '20241230' ipgo_dt, 'ROOM1' room_cd, '연필/지우개/휴지통' cus_info FROM dual
UNION ALL SELECT '20241230', 'ROOM2', '볼펜/메모/연필'   FROM dual
UNION ALL SELECT '20241230', 'ROOM3', '메모/건전지/볼펜' FROM dual
UNION ALL SELECT '20241231', 'ROOM3', '메모/건전지/볼펜' FROM dual
UNION ALL SELECT '20241231', 'ROOM4', '볼펜'             FROM dual
)
SELECT *
  FROM (SELECT a.ipgo_dt
             , b.item
             , LISTAGG(a.room_cd, ', ') WITHIN GROUP(ORDER BY a.room_cd) room_data
          FROM room_list a
             , item_list b
         WHERE INSTR('/'|| a.cus_info ||'/', '/'|| b.item ||'/') > 0
         GROUP BY a.ipgo_dt, b.item
        )
 PIVOT (MIN(room_data) FOR item IN ( '연필'   연필
                                   , '지우개' 지우개
                                   , '휴지통' 휴지통
                                   , '볼펜'   볼펜
                                   , '메모'   메모
                                   , '건전지' 건전지
                                   , '시계'   시계
                                   , '키보드' 키보드
                                   ) )
 ORDER BY ipgo_dt
;