데이터베이스 질문과 답변
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에 있는 쿼리를 활용하였습니다.)
by 주킹
[2017.09.25 15:17:25]
xpath를 활용하면 될듯한데 ..
쉽지는 않네요
by 마농
[2017.09.25 15:21:07]
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
;
-- http://www.gurubee.net/article/55512
) 버튼을 클릭하여 작성 하시면 됩니다.