데이터베이스 질문과 답변

PIVOT XML 시 정렬 문의드립니다. 1 4 9,343

by 동동동 [SQL Query] oracle pivot [2021.11.29 12:10:00]

안녕하세요... 동적으로 통계의 항목을 표시해야 해서  pivot xml을 사용하려고 하는데요..

pivot xml 시 칼럼의 순서를 원하는데로 줄수가 없나요?

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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]

아앗..마농님 빠른 답변 감사드립니다...

이미 답을 주셨는데... 떠 먹여 줘야 제가 알아 듣네요..ㅠㅠ

 

감사합니다...꾸벅...

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입