데이터베이스 질문과 답변
안녕하세요... 동적으로 통계의 항목을 표시해야 해서 pivot xml을 사용하려고 하는데요..
pivot xml 시 칼럼의 순서를 원하는데로 줄수가 없나요?
WITH
TMP_ROOM AS (
SELECT 'R1' AS RM_CD, 'A방' AS RM_NM, 1 AS RM_SEQ FROM DUAL UNION ALL
SELECT 'R2' AS RM_CD, 'B방' AS RM_NM, 2 AS RM_SEQ FROM DUAL UNION ALL
SELECT 'R3' AS RM_CD, 'C방' AS RM_NM, 3 AS RM_SEQ FROM DUAL
)
, TMP_DATA_MST AS (
SELECT * FROM (
SELECT 'D1' AS MST_CD, 'ABCDE' AS MST_NM, 2 AS MST_SEQ FROM DUAL UNION ALL
SELECT 'D2' AS MST_CD, '가나다' AS MST_NM, 1 AS MST_SEQ FROM DUAL UNION ALL
SELECT 'D3' AS MST_CD, 'AZZZ' AS MST_NM, 3 AS MST_SEQ FROM DUAL UNION ALL
SELECT 'D4' AS MST_CD, '[가나]다라' AS MST_NM, 4 AS MST_SEQ FROM DUAL UNION ALL
SELECT 'D5' AS MST_CD, '12ABC' AS MST_NM, 6 AS MST_SEQ FROM DUAL UNION ALL
SELECT 'D6' AS MST_CD, '가라다' AS MST_NM, 5 AS MST_SEQ FROM DUAL
)
ORDER BY MST_SEQ
)
, TMP_DATA AS (
SELECT 'R1' AS RM_CD, 'D1' AS MST_CD, '20211101' AS DY FROM DUAL UNION ALL
SELECT 'R1' AS RM_CD, 'D2' AS MST_CD, '20211103' AS DY FROM DUAL UNION ALL
SELECT 'R1' AS RM_CD, 'D3' AS MST_CD, '20211120' AS DY FROM DUAL UNION ALL
SELECT 'R2' AS RM_CD, 'D2' AS MST_CD, '20211115' AS DY FROM DUAL UNION ALL
SELECT 'R2' AS RM_CD, 'D2' AS MST_CD, '20211101' AS DY FROM DUAL UNION ALL
SELECT 'R2' AS RM_CD, 'D5' AS MST_CD, '20211122' AS DY FROM DUAL
)
SELECT RM_CD
, RM_NM
, MST_NM_XML
/* 이부분을 나중에 로직에서 처리 하려함 */
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[1]/column[1]') NM1
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[1]/column[2]') NM_CNT1
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[2]/column[1]') NM2
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[2]/column[2]') NM_CNT2
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[3]/column[1]') NM3
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[3]/column[2]') NM_CNT3
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[4]/column[1]') NM4
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[4]/column[2]') NM_CNT4
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[5]/column[1]') NM5
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[5]/column[2]') NM_CNT5
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[6]/column[1]') NM6
, EXTRACTVALUE(MST_NM_XML, '/PivotSet/item[6]/column[2]') NM_CNT6
FROM (
SELECT C.RM_CD
, MAX(C.RM_NM) AS RM_NM
, MAX(C.RM_SEQ) AS RM_SEQ
-- , A.MST_CD
, MAX(A.MST_NM) AS MST_NM
-- , MAX(A.MST_SEQ) AS MST_SEQ
, COUNT(B.MST_CD) CNT
FROM TMP_DATA_MST A
, TMP_DATA B
, TMP_ROOM C
WHERE A.MST_CD = B.MST_CD(+)
AND B.DY(+) >= '20211101'
AND B.DY(+) <= '20211130'
AND C.RM_CD = B.RM_CD(+)
GROUP BY C.RM_CD, A.MST_CD
ORDER BY RM_SEQ
-- , MST_SEQ
) ZZ
PIVOT XML (SUM(CNT) FOR MST_NM IN (SELECT MST_NM FROM TMP_DATA_MST)
);
다음과 같이 예제 쿼리에서 칼럼의 순서를 MST_SEQ 로 하려고 하는데 MST_NM 의 오름차순으로 밖에 안되네요..
별도의 정렬을 할 수 가 있는지요?
by 마농
[2021.11.29 13:20:22]
WITH tmp_room AS
(
SELECT 'R1' rm_cd, 'A방' rm_nm, 1 rm_seq FROM dual
UNION ALL SELECT 'R2', 'B방', 2 FROM dual
UNION ALL SELECT 'R3', 'C방', 3 FROM dual
)
, tmp_data_mst AS
(
SELECT 'D1' mst_cd, 'ABCDE' mst_nm, 2 mst_seq FROM dual
UNION ALL SELECT 'D2', '가나다' , 1 FROM dual
UNION ALL SELECT 'D3', 'AZZZ' , 3 FROM dual
UNION ALL SELECT 'D4', '[가나]다라', 4 FROM dual
UNION ALL SELECT 'D5', '12ABC' , 6 FROM dual
UNION ALL SELECT 'D6', '가라다' , 5 FROM dual
)
, tmp_data AS
(
SELECT 'R1' rm_cd, 'D1' mst_cd, '20211101' dy FROM dual
UNION ALL SELECT 'R1', 'D2', '20211103' FROM dual
UNION ALL SELECT 'R1', 'D3', '20211120' FROM dual
UNION ALL SELECT 'R2', 'D2', '20211115' FROM dual
UNION ALL SELECT 'R2', 'D2', '20211101' FROM dual
UNION ALL SELECT 'R2', 'D5', '20211122' FROM dual
)
SELECT *
FROM (SELECT a.rm_cd
, a.rm_nm
, b.rn
, b.mst_nm
, c.dy
FROM tmp_room a
CROSS JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY mst_seq) rn
, mst_cd, mst_nm, mst_seq
FROM tmp_data_mst
) b
LEFT OUTER JOIN tmp_data c
ON a.rm_cd = c.rm_cd
AND b.mst_cd = c.mst_cd
AND c.dy BETWEEN '20211101' AND '20211130'
)
PIVOT (MIN(mst_nm) nm, COUNT(dy) cnt
FOR rn IN (1, 2, 3, 4, 5, 6) -- 이 부분을 나중에 로직 처리 하세요.
)
;
by 동동동
[2021.11.29 13:47:35]
마농님 감사합니다..꾸벅...
실제 쿼리로는 EXTRACTVALUE 부분은 제외하고 MST_NM_XML 로 값을 받아와서
로직에서 xml파싱을 하려고 했는데...xml에서는 정렬이 불가하나 보네요..ㅠㅠ
답변 감사드립니다..^^
by 마농
[2021.11.29 14:17:04]
SELECT rm_cd
, rm_nm
, mst_seq_xml
, EXTRACTVALUE(mst_seq_xml, '/PivotSet/item[1]/column[2]') nm1
, EXTRACTVALUE(mst_seq_xml, '/PivotSet/item[1]/column[3]') nm_cnt1
-- , ...
FROM (SELECT a.rm_cd
, a.rm_nm
, b.mst_seq
, b.mst_nm
, c.dy
FROM tmp_room a
CROSS JOIN tmp_data_mst b
LEFT OUTER JOIN tmp_data c
ON a.rm_cd = c.rm_cd
AND b.mst_cd = c.mst_cd
AND c.dy BETWEEN '20211101' AND '20211130'
)
PIVOT XML (MIN(mst_nm) nm, COUNT(dy) cnt
FOR mst_seq IN (SELECT mst_seq FROM tmp_data_mst)
)
;
by 동동동
[2021.11.29 14:48:39]
아앗..마농님 빠른 답변 감사드립니다...
이미 답을 주셨는데... 떠 먹여 줘야 제가 알아 듣네요..ㅠㅠ
감사합니다...꾸벅...
) 버튼을 클릭하여 작성 하시면 됩니다.