데이터베이스 질문과 답변
안녕하세요.
쿼리 질문 드립니다.
PO 테이블은 각각의 PO_NO, PO_ITEM_NO 가 있습니다.
여기에 PO_REMARK 테이블을 조인하여 REMARK_VAL 를 SEQ 의 순서에 맞게 붙여서 표기하려고 합니다.
답장 부탁드립니다. (커서 사용하지 않은 쿼리 질문 입니다)
SELECT '0001' AS PO_NO, '10' AS PO_ITEM_NO FROM DUAL UNION ALL SELECT '0001' AS PO_NO, '20' AS PO_ITEM_NO FROM DUAL UNION ALL SELECT '0001' AS PO_NO, '30' AS PO_ITEM_NO FROM DUAL UNION ALL SELECT '0002' AS PO_NO, '10' AS PO_ITEM_NO FROM DUAL UNION ALL SELECT '0002' AS PO_NO, '20' AS PO_ITEM_NO FROM DUAL UNION ALL SELECT '0002' AS PO_NO, '30' AS PO_ITEM_NO FROM DUAL UNION ALL SELECT '0002' AS PO_NO, '40' AS PO_ITEM_NO FROM DUAL SELECT '0001' AS PO_NO, '1' AS SEQ, '아름다운' AS REMARK_VAL FROM DUAL UNION ALL SELECT '0001' AS PO_NO, '2' AS SEQ, '사람은' AS REMARK_VAL FROM DUAL UNION ALL SELECT '0001' AS PO_NO, '3' AS SEQ, '영원히' AS REMARK_VAL FROM DUAL UNION ALL SELECT '0001' AS PO_NO, '4' AS SEQ, '아름답다' AS REMARK_VAL FROM DUAL UNION ALL SELECT '0001' AS PO_NO, '5' AS SEQ, '진짜' AS REMARK_VAL FROM DUAL UNION ALL SELECT '0001' AS PO_NO, '6' AS SEQ, '영원히' AS REMARK_VAL FROM DUAL UNION ALL SELECT '0002' AS PO_NO, '1' AS SEQ, '나도' AS REMARK_VAL FROM DUAL UNION ALL SELECT '0002' AS PO_NO, '2' AS SEQ, '아름답다' AS REMARK_VAL FROM DUAL UNION ALL SELECT '0002' AS PO_NO, '3' AS SEQ, '정말' AS REMARK_VAL FROM DUAL UNION ALL SELECT '0002' AS PO_NO, '4' AS SEQ, '진짜' AS REMARK_VAL FROM DUAL
by jkson
[2017.07.11 13:56:35]
WITH PO AS
(
SELECT '0001' AS PO_NO, '10' AS PO_ITEM_NO FROM DUAL UNION ALL
SELECT '0001' AS PO_NO, '20' AS PO_ITEM_NO FROM DUAL UNION ALL
SELECT '0001' AS PO_NO, '30' AS PO_ITEM_NO FROM DUAL UNION ALL
SELECT '0002' AS PO_NO, '10' AS PO_ITEM_NO FROM DUAL UNION ALL
SELECT '0002' AS PO_NO, '20' AS PO_ITEM_NO FROM DUAL UNION ALL
SELECT '0002' AS PO_NO, '30' AS PO_ITEM_NO FROM DUAL UNION ALL
SELECT '0002' AS PO_NO, '40' AS PO_ITEM_NO FROM DUAL
)
, PO_REMARK
AS
(
SELECT '0001' AS PO_NO, '1' AS SEQ, '아름다운' AS REMARK_VAL FROM DUAL UNION ALL
SELECT '0001' AS PO_NO, '2' AS SEQ, '사람은' AS REMARK_VAL FROM DUAL UNION ALL
SELECT '0001' AS PO_NO, '3' AS SEQ, '영원히' AS REMARK_VAL FROM DUAL UNION ALL
SELECT '0001' AS PO_NO, '4' AS SEQ, '아름답다' AS REMARK_VAL FROM DUAL UNION ALL
SELECT '0001' AS PO_NO, '5' AS SEQ, '진짜' AS REMARK_VAL FROM DUAL UNION ALL
SELECT '0001' AS PO_NO, '6' AS SEQ, '영원히' AS REMARK_VAL FROM DUAL UNION ALL
SELECT '0002' AS PO_NO, '1' AS SEQ, '나도' AS REMARK_VAL FROM DUAL UNION ALL
SELECT '0002' AS PO_NO, '2' AS SEQ, '아름답다' AS REMARK_VAL FROM DUAL UNION ALL
SELECT '0002' AS PO_NO, '3' AS SEQ, '정말' AS REMARK_VAL FROM DUAL UNION ALL
SELECT '0002' AS PO_NO, '4' AS SEQ, '진짜' AS REMARK_VAL FROM DUAL
)
SELECT PO_NO, PO_ITEM_NO
, (SELECT SUBSTR(XMLAGG(XMLELEMENT(X,'',REMARK_VAL) ORDER BY SEQ).EXTRACT('//text()'),1)
FROM PO_REMARK B
WHERE B.PO_NO = A.PO_NO) RMK_9I
, (SELECT LISTAGG(REMARK_VAL) WITHIN GROUP (ORDER BY SEQ)
FROM PO_REMARK B
WHERE B.PO_NO = A.PO_NO) RMK_11G
FROM PO A
by 잭키올
[2017.07.11 14:14:21]
오라클로 쿼리를 짜는 것이 아니라 MSSQL 로 짜서 그런데 LISTAGG 함수를 사용하지 않고 짜는 방법은 없을까요..?
by jkson
[2017.07.11 14:26:55]
WITH PO AS
(
SELECT '0001' AS PO_NO, '10' AS PO_ITEM_NO UNION ALL
SELECT '0001' AS PO_NO, '20' AS PO_ITEM_NO UNION ALL
SELECT '0001' AS PO_NO, '30' AS PO_ITEM_NO UNION ALL
SELECT '0002' AS PO_NO, '10' AS PO_ITEM_NO UNION ALL
SELECT '0002' AS PO_NO, '20' AS PO_ITEM_NO UNION ALL
SELECT '0002' AS PO_NO, '30' AS PO_ITEM_NO UNION ALL
SELECT '0002' AS PO_NO, '40' AS PO_ITEM_NO
)
, PO_REMARK
AS
(
SELECT '0001' AS PO_NO, '1' AS SEQ, '아름다운' AS REMARK_VAL UNION ALL
SELECT '0001' AS PO_NO, '2' AS SEQ, '사람은' AS REMARK_VAL UNION ALL
SELECT '0001' AS PO_NO, '3' AS SEQ, '영원히' AS REMARK_VAL UNION ALL
SELECT '0001' AS PO_NO, '4' AS SEQ, '아름답다' AS REMARK_VAL UNION ALL
SELECT '0001' AS PO_NO, '5' AS SEQ, '진짜' AS REMARK_VAL UNION ALL
SELECT '0001' AS PO_NO, '6' AS SEQ, '영원히' AS REMARK_VAL UNION ALL
SELECT '0002' AS PO_NO, '1' AS SEQ, '나도' AS REMARK_VAL UNION ALL
SELECT '0002' AS PO_NO, '2' AS SEQ, '아름답다' AS REMARK_VAL UNION ALL
SELECT '0002' AS PO_NO, '3' AS SEQ, '정말' AS REMARK_VAL UNION ALL
SELECT '0002' AS PO_NO, '4' AS SEQ, '진짜' AS REMARK_VAL
)
SELECT PO_NO, PO_ITEM_NO
, (SELECT '' + REMARK_VAL
FROM PO_REMARK B
WHERE B.PO_NO = A.PO_NO
ORDER BY SEQ
FOR XML PATH('')) RMK
FROM PO A
http://www.gurubee.net/article/55512
마농님 댓글 참조
STUFF은 뭔지 연구중입니당
=>지정한 자리수의 글자를 특정 글자로 REPLACE하는 기능 같네요.
여기서는 구분자 없이 붙여주니 없어도 될 것 같네요.
by 잭키올
[2017.07.11 14:37:20]
감사합니다.
이중 루프 돌리고, DISTINCT 해서 채번하고.. 난리를 쳐봤습니다.
뭐 결과는 원하는 답이 나옵니다만 쿼리스럽지 못하다는 생각이 들어서
질문을 올렸는데.. 역시나 간결한 쿼리가 나오는군요..
정말 감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.