꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 문의드립니다.(그룹별 행고정)

by 김인덕 [SQL Query] [2015.09.02 17:27:36]

WITH A AS (
	SELECT TO_DATE('20150101', 'YYYYMMDD') AS T_DATE, '11' AS A_CD, '1' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
	SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '11' AS A_CD, '1' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
	SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '11' AS A_CD, '3' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
	SELECT TO_DATE('20150103', 'YYYYMMDD') AS T_DATE, '11' AS A_CD, '3' AS TP_CD, '14' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
	SELECT TO_DATE('20150201', 'YYYYMMDD') AS T_DATE, '11' AS A_CD, '1' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '21' AS A_CD, '1' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '21' AS A_CD, '1' AS TP_CD, '14' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '21' AS A_CD, '1' AS TP_CD, '18' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '21' AS A_CD, '1' AS TP_CD, '12' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '21' AS A_CD, '1' AS TP_CD, '16' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '21' AS A_CD, '1' AS TP_CD, '19' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '21' AS A_CD, '3' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '21' AS A_CD, '3' AS TP_CD, '14' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150202', 'YYYYMMDD') AS T_DATE, '21' AS A_CD, '3' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '22' AS A_CD, '1' AS TP_CD, '12' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '22' AS A_CD, '3' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '31' AS A_CD, '1' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '31' AS A_CD, '3' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150102', 'YYYYMMDD') AS T_DATE, '31' AS A_CD, '4' AS TP_CD, '10' AS SCT_CD, '1' AS QTY FROM DUAL UNION ALL
    SELECT TO_DATE('20150103', 'YYYYMMDD') AS T_DATE, '31' AS A_CD, '3' AS TP_CD, '14' AS SCT_CD, '1' AS QTY FROM DUAL
)
SELECT
    TO_CHAR(T_DATE, 'YYYYMM') AS YYYYMM, A_CD, TP_CD
    ,NVL(SUM(DECODE(SCT_CD,'10', QTY)), 0) AS SCT_10
    ,NVL(SUM(DECODE(SCT_CD,'14', QTY)), 0) AS SCT_14
    ,NVL(SUM(DECODE(SCT_CD,'18', QTY)), 0) AS SCT_18
    ,NVL(SUM(DECODE(SCT_CD,'12', QTY)), 0) AS SCT_12
    ,NVL(SUM(DECODE(SCT_CD,'16', QTY)), 0) AS SCT_16
    ,NVL(SUM(DECODE(SCT_CD,'19', QTY)), 0) AS SCT_19
FROM (
    SELECT
        *
    FROM A
)
GROUP BY
    TO_CHAR(T_DATE, 'YYYYMM'), A_CD, TP_CD
;

>>>>>>> 현재 결과

YYYYMM A_CD TP_CD SCT_10 SCT_14 SCT_18 SCT_12 SCT_16 SCT_19
201501 11 1 2 0 0 0 0 0
201501 11 3 1 1 0 0 0 0
201501 21 1 1 1 1 1 1 1
201501 21 3 1 1 0 0 0 0
201501 22 1 0 0 0 1 0 0
201501 22 3 1 0 0 0 0 0
201501 31 1 1 0 0 0 0 0
201501 31 3 1 1 0 0 0 0
201501 31 4 1 0 0 0 0 0
201502 11 1 1 0 0 0 0 0
201502 21 3 1 0 0 0 0 0

>>>>>>> 원하는 결과

YYYYMM A_CD TP_CD SCT_10 SCT_14 SCT_18 SCT_12 SCT_16 SCT_19
201501 11 1 2 0 0 0 0 0
201501 11 3 1 1 0 0 0 0
201501 11 4 0 0 0 0 0 0 << 추가
201501 21 1 1 1 1 1 1 1
201501 21 3 1 1 0 0 0 0
201501 21 4 0 0 0 0 0 0 << 추가
201501 22 1 0 0 0 1 0 0
201501 22 3 1 0 0 0 0 0
201501 22 4 0 0 0 0 0 0 << 추가
201501 31 1 1 0 0 0 0 0
201501 31 3 1 1 0 0 0 0
201501 31 4 1 0 0 0 0 0
201502 11 1 1 0 0 0 0 0
201502 11 3 0 0 0 0 0 0 << 추가
201502 11 4 0 0 0 0 0 0 << 추가
201502 21 1 0 0 0 0 0 0 << 추가
201502 21 3 1 0 0 0 0 0
201502 21 4 0 0 0 0 0 0 << 추가
201502 22 1 0 0 0 0 0 0 << 추가
201502 22 3 0 0 0 0 0 0 << 추가
201502 22 4 0 0 0 0 0 0 << 추가
201502 31 1 0 0 0 0 0 0 << 추가
201502 31 3 0 0 0 0 0 0 << 추가
201502 31 4 0 0 0 0 0 0 << 추가

위 원하는 결과로
- 월별
- A_CD (11, 21, 22, 31) 고정
- TP_CD(1, 3, 4) 고정
으로 빠진 부분을 추가하여 나타냈으면 합니다.

고수님들 부탁드립니다.

by 마농 [2015.09.02 18:38:38]

SELECT c.ym, a.a_cd, b.tp_cd
     , NVL(sct10, 0) sct10
     , NVL(sct14, 0) sct14
     , NVL(sct18, 0) sct18
     , NVL(sct12, 0) sct12
     , NVL(sct16, 0) sct16
     , NVL(sct19, 0) sct19
  FROM (SELECT '11' a_cd FROM dual
        UNION ALL SELECT '21' FROM dual
        UNION ALL SELECT '22' FROM dual
        UNION ALL SELECT '31' FROM dual
        ) a
 CROSS JOIN
       (SELECT '1' tp_cd FROM dual
        UNION ALL SELECT '3' FROM dual
        UNION ALL SELECT '4' FROM dual
        ) b
  LEFT OUTER JOIN
       (SELECT TO_CHAR(t_date, 'yyyymm') ym
             , a_cd, tp_cd
             , SUM(DECODE(sct_cd, '10', qty)) sct10
             , SUM(DECODE(sct_cd, '14', qty)) sct14
             , SUM(DECODE(sct_cd, '18', qty)) sct18
             , SUM(DECODE(sct_cd, '12', qty)) sct12
             , SUM(DECODE(sct_cd, '16', qty)) sct16
             , SUM(DECODE(sct_cd, '19', qty)) sct19
          FROM a
         GROUP BY TO_CHAR(t_date, 'yyyymm'), a_cd, tp_cd
        ) c
  PARTITION BY (c.ym)
    ON a.a_cd  = c.a_cd
   AND b.tp_cd = c.tp_cd
 ORDER BY c.ym, a.a_cd, b.tp_cd
;

by 김인덕 [2015.09.03 10:20:19]

마농님 정말 감사드립니다. 더 많은 공부가 필요한걸 절실히 느꼈습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변