WITH tbl_common(val1, text1) AS
(
SELECT '01', '사과' FROM dual
UNION ALL SELECT '02', '배' FROM dual
UNION ALL SELECT '03', '딸기' FROM dual
)
, tbl(column1) AS
(
SELECT '01,02,03' FROM dual
UNION ALL SELECT '01,03' column1 FROM dual
)
SELECT column1
, (SELECT SUBSTR(XMLAGG(XMLELEMENT(x, ',', text1)).EXTRACT('//text()'), 2)
FROM tbl_common
WHERE a.column1 LIKE '%'||val1||'%'
) text2
FROM tbl a
;
위 쿼리중
SUBSTR(XMLAGG(XMLELEMENT(x, ',', text1)).EXTRACT('//text()'), 2)
부분을 POSTGRESQL로 변환하려면 어떻게 해야하나요?
(쿼리는 Q&A에 있는 쿼리를 활용하였습니다.)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | WITH tbl_common(val1, text1) AS ( SELECT '01' , '사과' UNION ALL SELECT '02' , '배' UNION ALL SELECT '03' , '딸기' ) , tbl(column1) AS ( SELECT '01,02,03' UNION ALL SELECT '01,03' column1 ) SELECT a.column1 , ( SELECT Array_to_String(Array_Agg(text1), ',' ) FROM tbl_common b WHERE POSITION(b.val1 IN a.column1) > 0 ) text2 FROM tbl a ; |