데이터베이스 질문과 답변
TMP1 SELECT 'A01' AS CODE, 'NM1' AS CONM FROM DUAL UNION ALL SELECT 'A02' AS CODE, 'NM2' AS CONM FROM DUAL UNION ALL SELECT 'A03' AS CODE, 'NM3' AS CONM FROM DUAL UNION ALL SELECT 'A04' AS CODE, 'NM4' AS CONM FROM DUAL ; TMP2 SELECT 'U01' AS UNAME, 'A01' AS CODE, 2 AS QTY FROM DUAL UNION ALL SELECT 'U01' AS UNAME, 'A03' AS CODE, 1 AS QTY FROM DUAL UNION ALL SELECT 'U04' AS UNAME, 'A01' AS CODE, 1 AS QTY FROM DUAL 위의 데이터 로 밑에처럼 쿼리를 짜야하는데 어떻게 짜야할지 모르겠어요...도움요청해요 ㅠㅠ 부탁드립니다. 나름 정리한다고 정리했는데 이상한 부분있으면 말 해주세요~ㅠㅠ UNAME CONM_QTY U01 NM1:2,NM2:0,NM3:1,NM4:0 U04 NM1:1,NM2:0,NM3:0,NM4:0
by 마농
[2021.03.04 11:23:29]
WITH tmp1 AS
(
SELECT 'A01' code, 'NM1' conm FROM dual
UNION ALL SELECT 'A02', 'NM2' FROM dual
UNION ALL SELECT 'A03', 'NM3' FROM dual
UNION ALL SELECT 'A04', 'NM4' FROM dual
)
, tmp2 AS
(
SELECT 'U01' uname, 'A01' code, 2 qty FROM dual
UNION ALL SELECT 'U01', 'A03', 1 FROM dual
UNION ALL SELECT 'U04', 'A01', 1 FROM dual
)
SELECT b.uname
, LISTAGG(a.conm ||':'|| NVL(b.qty, 0), ',') WITHIN GROUP(ORDER BY a.code) conm_qty
FROM tmp1 a
LEFT OUTER JOIN tmp2 b
PARTITION BY (b.uname)
ON a.code = b.code
GROUP BY b.uname
ORDER BY b.uname
;
by 휘내님
[2021.03.04 12:03:57]
와정말 감사합니다 ㅠㅠㅠ!!!!!
) 버튼을 클릭하여 작성 하시면 됩니다.