CREATE TABLE HCDT_ACTIVITYCOSTSUM
             (YYMM_YM VARCHAR2(6),
              SITE_CD VARCHAR2(6),
              DEPT_CD VARCHAR2(6),
              ACTIVITY_CD VARCHAR2(6),
              CHG_TY VARCHAR2(20),
              BUDGET_CD VARCHAR2(8),
              AMT_AM NUMBER,
              ACCOUNT_CD VARCHAR2(6));
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63021000',891400,'630210');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63031104',145000,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63031201',504400,'630312');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63039901',498000,'630399');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031105',130974,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031106',172671,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031117',199509,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031118',415049,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031301',454371,'630313');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031302',546924,'630313');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031801',560342,'630318');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63032303',114270,'630323');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63032401',297352,'630324');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES ('199802','111','A00100','112101','C5','63032505',107242,'630325');

CREATE TABLE HCDT_OUTPUTQTY
            (YYMM_YM VARCHAR2(6),
             SITE_CD VARCHAR2(6),
             DEPT_CD VARCHAR2(6),
             ACTIVITY_CD VARCHAR2(6),
             DRIVER_CD VARCHAR2(6),
             QTY_QN NUMBER,
             UNIT  VARCHAR2(20));
INSERT INTO HCDT_OUTPUTQTY VALUES ('199802','111','A00100','112101','112101',900,NULL);

REPORT 형식 맞추기



건
    다음 테이블은 활동에대한 금액을 가지고 있다.
    DESC HCDT_ACTIVITYCOSTSUM

    열 이름                        널?      유형
    ------------------------------ -------- ----
    YYMM_YM                        NOT NULL VARCHAR2(6)  <-- 년월
    SITE_CD                        NOT NULL VARCHAR2(6)  <-- 사업장
    DEPT_CD                        NOT NULL VARCHAR2(6)  <-- 부서
    ACTIVITY_CD                    NOT NULL VARCHAR2(6)  <-- 활동
    CHG_TY                         NOT NULL VARCHAR2(20) <-- 구분
    BUDGET_CD                      NOT NULL VARCHAR2(8)  <-- 항목
    AMT_AM                                  NUMBER       <-- 활동금액
    ACCOUNT_CD                     NOT NULL VARCHAR2(6)  <-- 계정
  
    YYMM_Y SITE_C DEPT_C ACTIVI CHG_TY          BUDGET_C AMT_AM     ACCOUN
    ------ ------ ------ ------ --------------- -------- ---------- ------
    199802 111    A00100 112101 C1              63021000   79140000 630210
    199802 111    A00100 112101 C1              63031104    1750000 630311
    199802 111    A00100 112101 C1              63031201     500400 630312
    199802 111    A00100 112101 C1              63039901     498000 630399
    199802 111    A00100 112101 C5              63031105   13081974 630311
    199802 111    A00100 112101 C5              63031106   15172671 630311
    199802 111    A00100 112101 C5              63031117    3199509 630311
    199802 111    A00100 112101 C5              63031118      15049 630311
    199802 111    A00100 112101 C5              63031301    4454371 630313
    199802 111    A00100 112101 C5              63031302    5866924 630313
    199802 111    A00100 112101 C5              63031801   56980342 630318
    199802 111    A00100 112101 C5              63032303    1197270 630323
    199802 111    A00100 112101 C5              63032401   29721352 630324
    199802 111    A00100 112101 C5              63032505    1073242 630325
  
    다음 테이블은 활동에대한 활동량을 가지고 있다.
    DESC HCDT_OUTPUTQTY
  
    열 이름                        널?      유형
    ------------------------------ -------- ----
    YYMM_YM                        NOT NULL VARCHAR2(6) <-- 년월
    SITE_CD                        NOT NULL VARCHAR2(6) <-- 사업장
    DEPT_CD                        NOT NULL VARCHAR2(6) <-- 부서
    ACTIVITY_CD                    NOT NULL VARCHAR2(6) <-- 활동
    DRIVER_CD                      NOT NULL VARCHAR2(6) <-- 활동량
    QTY_QN                                  NUMBER      <-- 단위
    UNIT                                    VARCHAR2(20)
  
    YYMM_Y SITE_C DEPT_C ACTIVI DRIVER QTY_QN     UNIT                
    ------ ------ ------ ------ ------ ---------- --------------------
    199802 111    A00100 112101 112101        900                    
  
문제.

    활동/산출물/활동원가,활동량,활동단가구분 에 따라 주어진 기간 사이의
    해당값 들을 월별로 보여주고 제일 앞에 합계를 보여준다.

    ACTIVI DRIVER G_KEY  G_DESC   합계    M      M+1    M+2     M+3    M+4    M+5    M+6    M+7    +8    M+9    M+10   M+11
    ------ ------ ------ -------- ------- ------ ------- ------ ------ ------ ------ ------ ------ ----- ------ ------ ------
    112101 112101      1 활동원가 5037504        5037504    112101 112101      2 활동량       900            900
    112101 112101      3 활동단가    5597           5597
    활동2  산출물2     1 활동원가  000000 000000  000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
    활동2  산출물2     2 활동량    000000 000000  000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
    활동2  산출물2     3 활동단가  000000 000000  000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
    활동3  산출물3     1 활동원가  000000 000000  000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
    활동3  산출물3     2 활동량    000000 000000  000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
    활동3  산출물3     3 활동단가  000000 000000  000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000




  와 같은 형식의 결과를 나오게 하면된다.

    이때 활동단가 = 활동원가/활동량 이다
         합계란은 1번째 값부터 12번째 값의 합이며
         합계 활동단가 = 합계활동원가/합계활동량 이다
    첫월과 마지막월이 12개월을 초과할 시는 첫월을 기준으로
    12개월만큼의 DATA만을 이용하여 결과를 보여준다.
    결과의 이탤릭체 부분은 가상 데이타 이다.

생각.
    한눈에 결과를 봐도 구현하는데 가장 문제가 될듯한 부분은 합계를  가로가 아닌
    세로축에 보여 주어야 한다는 것이다.
    이미 월별로 값을 COLUMN으로 나열하는 기법이나,구분자를 두어 값을 ROW형식으로
    보여주는 기법은 앞 주제에서 다루었다.
    이번에는 월별 값을 ROW형식에서 COLUMN형식으로 바꿔주고,동시에 구분에 따라
    활동원가/활동량/활동단가 를 ROW형식으로 보여주는 기법이 모두 필요하다.
    합계를 세로축에 보여주는 것도 가로축에 합계를 보여주는 기법을 약간만
    변형하면된다.

해법.
    단계1.먼저 활동원가를 가져오기 위하여 HCDT_ACTIVITYCOSTSUM 테이블에서 CHG_TY
          를 배제하고 SUM을 한다.
          SELECT YYMM_YM,
                 SITE_CD,
                 DEPT_CD,
                 ACTIVITY_CD,
                 SUM(AMT_AM) AMT_AM
          FROM   HCDT_ACTIVITYCOSTSUM
          WHERE  YYMM_YM BETWEEN '199801'
                         AND     LEAST('199810',TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM')
                                 ,11),'YYYYMM'))
          AND    SITE_CD  = '111'
          AND    DEPT_CD  = 'A00100'
          GROUP BY
                 YYMM_YM,
                 SITE_CD,
                 DEPT_CD,
                 ACTIVITY_CD

          결과는 아래와 같다.

          YYMM_Y SITE_C DEPT_C ACTIVI AMT_AM    
          ------ ------ ------ ------ ----------
          199802 111    A00100 112101  212651104

          이때
           LEAST('199810',TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),11),'YYYYMM'))
          부분은 시작월을 기준으로 12개월을 초과할 경우 12개월분 DATA만을
          읽어오도록 하기위한 문장이다.
      
    단계2.단계1의 결과를 이용해 HCDT_OUTPUTQTY 와 JOIN을 걸어 원가/량/단가를 모두 구한다.
          SELECT C.YYMM_YM,
                 C.ACTIVITY_CD,
                 C.DRIVER_CD,
                 A.AMT_AM,
                 C.QTY_QN,
                 DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
          FROM ( SELECT  YYMM_YM,
                         SITE_CD,
                         DEPT_CD,
                         ACTIVITY_CD,
                         SUM(AMT_AM) AMT_AM
                  FROM   HCDT_ACTIVITYCOSTSUM
                  WHERE  YYMM_YM BETWEEN '199801'
                                 AND LEAST('199810',TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM')
                                          ,11),'YYYYMM'))
                  AND    SITE_CD  = '111'
                  AND    DEPT_CD  = 'A00100'
                  GROUP BY
                         YYMM_YM,
                         SITE_CD,
                         DEPT_CD,
                         ACTIVITY_CD
               ) A,
               HCDT_OUTPUTQTY C
           WHERE  C.YYMM_YM     = A.YYMM_YM
           AND    C.SITE_CD     = A.SITE_CD
           AND    C.DEPT_CD     = A.DEPT_CD
           AND    C.ACTIVITY_CD = A.ACTIVITY_CD
           AND    C.DRIVER_CD   > ' '

           결과는 아래와 같다.

           YYMM_Y ACTIVI DRIVER AMT_AM     QTY_QN     UNIT_COST
           ------ ------ ------ ---------- ---------- ----------
           199802 112101 112101  212651104        900 236279.004
        단계3.단계2의 결과를 이용해 합계를 추가해 준다.
              방법은 복제 테이블을 이용해 합계를 구하는 방식 그대로 이다.
              단지 합계라는 월이 하나 더 생긴것 같은 결과가 될 것이다.      
    
           SELECT DECODE(NO,1,YYMM_YM,2,'합계') YYMM_YM,
                  ACTIVITY_CD,
                  DRIVER_CD,
                  SUM(AMT_AM)     AMT_AM,
                  SUM(QTY_QN)     QTY_QN,
                  SUM(AMT_AM)/SUM(QTY_QN)  UNIT_COST
           FROM (SELECT C.YYMM_YM,
                        C.ACTIVITY_CD,
                        C.DRIVER_CD,
                        A.AMT_AM,
                        C.QTY_QN,
                        DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
                 FROM ( SELECT  YYMM_YM,
                                SITE_CD,
                                DEPT_CD,
                                ACTIVITY_CD,
                                SUM(AMT_AM) AMT_AM
                         FROM   HCDT_ACTIVITYCOSTSUM
                         WHERE  YYMM_YM BETWEEN '199801'
                                        AND LEAST('199810',
                                                  TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM')
                                                  ,11),'YYYYMM'))
                         AND    SITE_CD  = '111'
                         AND    DEPT_CD  = 'A00100'
                         GROUP BY
                                YYMM_YM,
                                SITE_CD,
                                DEPT_CD,
                                ACTIVITY_CD
                      ) A,
                        HCDT_OUTPUTQTY C
                 WHERE  C.YYMM_YM     = A.YYMM_YM
                 AND    C.SITE_CD     = A.SITE_CD
                 AND    C.DEPT_CD     = A.DEPT_CD
                 AND    C.ACTIVITY_CD = A.ACTIVITY_CD
                 AND    C.DRIVER_CD   > ' '
                ) F1,
                (SELECT ROWNUM NO FROM USER_TABLES WHERE ROWNUM < 3) F2
             GROUP BY
                   DECODE(NO,1,YYMM_YM,2,'합계'),
                   ACTIVITY_CD,
                   DRIVER_CD

          결과는 아래와 같다.

          YYMM_Y ACTIVI DRIVER AMT_AM     QTY_QN     UNIT_COST
          ------ ------ ------ ---------- ---------- ----------
          199802 112101 112101  212651104        900 236279.004
          합계   112101 112101  212651104        900 236279.004
    단계4.이번단계는 이제까지의 결과를 이용해 활동원가,활동량,활동단가를
          ROW형식으로 바꿔주는 일이다.
          앞의 주제에서도 다룬적이 있듯이 3개의 ROW를 가진 복제용 TABLE과의
          CARTESIAN PRODUCT를 이용한다.

          SELECT YYMM_YM,
                 ACTIVITY_CD,
                 DRIVER_CD,
                 RNO                                              G_KEY,
                 DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가') G_DESC,
                 SUM(DECODE(RNO,1,AMT_AM,2,QTY_QN,3,UNIT_COST))   AMT_AM
          FROM ( SELECT DECODE(NO,1,YYMM_YM,2,'합계') YYMM_YM,
                        ACTIVITY_CD,
                        DRIVER_CD,
                        SUM(AMT_AM)     AMT_AM,
                        SUM(QTY_QN)     QTY_QN,
                        SUM(AMT_AM)/SUM(QTY_QN)  UNIT_COST
                 FROM (SELECT C.YYMM_YM,
                              C.ACTIVITY_CD,
                              C.DRIVER_CD,
                              A.AMT_AM,
                              C.QTY_QN,
                              DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
                       FROM ( SELECT  YYMM_YM,
                                      SITE_CD,
                                      DEPT_CD,
                                      ACTIVITY_CD,
                                      SUM(AMT_AM) AMT_AM
                               FROM   HCDT_ACTIVITYCOSTSUM
                               WHERE  YYMM_YM BETWEEN '199801'
                                              AND   LEAST('199810',
                                                         TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'
                                                         ),11),'YYYYMM'))
                               AND    SITE_CD  = '111'
                               AND    DEPT_CD  = 'A00100'
                               GROUP BY
                                      YYMM_YM,
                                      SITE_CD,
                                      DEPT_CD,
                                      ACTIVITY_CD
                            ) A,
                              HCDT_OUTPUTQTY C
                        WHERE  C.YYMM_YM     = A.YYMM_YM
                        AND    C.SITE_CD     = A.SITE_CD
                        AND    C.DEPT_CD     = A.DEPT_CD
                        AND    C.ACTIVITY_CD = A.ACTIVITY_CD
                        AND    C.DRIVER_CD   > ' '
                      ) F1,
                      (SELECT ROWNUM NO FROM USER_TABLES WHERE ROWNUM < 3) F2
                  GROUP BY
                        DECODE(NO,1,YYMM_YM,2,'합계'),
                        ACTIVITY_CD,
                        DRIVER_CD
                ) G1,
                ( SELECT ROWNUM RNO FROM USER_TABLES WHERE ROWNUM <= 3) G2
           GROUP BY
                  YYMM_YM,
                 ACTIVITY_CD,
                 DRIVER_CD,
                 RNO,
                 DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가')

          결과는 아래와 같다.

          YYMM_Y ACTIVI DRIVER G_KEY      G_DESC   AMT_AM    
          ------ ------ ------ ---------- -------- ----------
          199802 112101 112101          1 활동원가  212651104
          199802 112101 112101          2 활동량          900
          199802 112101 112101          3 활동단가 236279.004
          합계   112101 112101          1 활동원가  212651104
          합계   112101 112101          2 활동량          900
          합계   112101 112101          3 활동단가 236279.004
    단계5.마지막으로 할일은 결과를 보여주기위한 형식으로 FORMAT을 맞추는 일이다.
          최종문장은 아래와 같다.

          SELECT  A.ACTIVITY_CD,
                  A.DRIVER_CD,
                  G_KEY,
                  G_DESC,
                  SUM(DECODE(A.YYMM_YM,'합계',A.AMT_AM))      합계,
                  SUM(DECODE(A.YYMM_YM,'199801',A.AMT_AM))    M,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      1),'YYYYMM'),A.AMT_AM)) M01,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      2),'YYYYMM'),A.AMT_AM)) M02,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      3),'YYYYMM'),A.AMT_AM)) M03,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      4),'YYYYMM'),A.AMT_AM)) M04,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      5),'YYYYMM'),A.AMT_AM)) M05,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      6),'YYYYMM'),A.AMT_AM)) M06,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      7),'YYYYMM'),A.AMT_AM)) M07,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      8),'YYYYMM'),A.AMT_AM)) M08,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      9),'YYYYMM'),A.AMT_AM)) M09,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      10),'YYYYMM'),A.AMT_AM)) M10,
                  SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                      11),'YYYYMM'),A.AMT_AM)) M11
          FROM  ( SELECT YYMM_YM,
                         ACTIVITY_CD,
                         DRIVER_CD,
                         RNO                                              G_KEY,
                         DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가') G_DESC,
                         SUM(DECODE(RNO,1,AMT_AM,2,QTY_QN,3,UNIT_COST))   AMT_AM
                  FROM ( SELECT DECODE(NO,1,YYMM_YM,2,'합계') YYMM_YM,
                                ACTIVITY_CD,
                                DRIVER_CD,
                                SUM(AMT_AM)     AMT_AM,
                                SUM(QTY_QN)     QTY_QN,
                                SUM(AMT_AM)/SUM(QTY_QN)  UNIT_COST
                         FROM (SELECT C.YYMM_YM,
                                      C.ACTIVITY_CD,
                                      C.DRIVER_CD,
                                      A.AMT_AM,
                                      C.QTY_QN,
                                      DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
                               FROM ( SELECT  YYMM_YM,
                                              SITE_CD,
                                              DEPT_CD,
                                              ACTIVITY_CD,
                                              SUM(AMT_AM) AMT_AM
                                       FROM   HCDT_ACTIVITYCOSTSUM
                                       WHERE  YYMM_YM BETWEEN '199801'
                                                      AND   LEAST('199810',
                                                       TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
                                                            11),'YYYYMM'))
                                       AND    SITE_CD  = '111'
                                       AND    DEPT_CD  = 'A00100'
                                       GROUP BY
                                              YYMM_YM,
                                              SITE_CD,
                                              DEPT_CD,
                                              ACTIVITY_CD
                                    ) A,
                                      HCDT_OUTPUTQTY C
                                WHERE  C.YYMM_YM     = A.YYMM_YM
                                AND    C.SITE_CD     = A.SITE_CD
                                AND    C.DEPT_CD     = A.DEPT_CD
                                AND    C.ACTIVITY_CD = A.ACTIVITY_CD
                                AND    C.DRIVER_CD   > ' '
                              ) F1,
                              (SELECT ROWNUM NO FROM USER_TABLES WHERE ROWNUM < 3) F2
                          GROUP BY
                                DECODE(NO,1,YYMM_YM,2,'합계'),
                                ACTIVITY_CD,
                                DRIVER_CD
                        ) G1,
                        ( SELECT ROWNUM RNO FROM USER_TABLES WHERE ROWNUM <= 3) G2
                   GROUP BY
                          YYMM_YM,
                         ACTIVITY_CD,
                         DRIVER_CD,
                         RNO,
                         DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가')
                 ) A
            GROUP BY
                  A.ACTIVITY_CD,
                  A.DRIVER_CD,
                  G_KEY,
                  G_DESC

뒷풀이.
    위의 풀이에서는 시작월을   '199801'
                    마지막월을 '199810' 으로하였다.
    하지만 두개의 월과 사업장,부서를 PARAMETER화 하면 유연한 QUERY로 구성할 수 있다.