CREATE TABLE TEST43 (YMD VARCHAR2(06),     ITEM_CD VARCHAR2(06),
                     TYPE_CD VARCHAR2(01), AMT     NUMBER);
CREATE INDEX TEST43_I ON TEST43(YMD,ITEM_CD,TYPE_CD);
INSERT INTO TEST43 VALUES ('199801','PART01','B',4300);
INSERT INTO TEST43 VALUES ('199801','PART01','C',3300);
INSERT INTO TEST43 VALUES ('199802','PART01','B',4200);
INSERT INTO TEST43 VALUES ('199802','PART01','C',3500);
INSERT INTO TEST43 VALUES ('199803','PART01','B',5300);
INSERT INTO TEST43 VALUES ('199803','PART01','C',2300);
INSERT INTO TEST43 VALUES ('199804','PART01','B',2500);
INSERT INTO TEST43 VALUES ('199804','PART01','C',3300);
INSERT INTO TEST43 VALUES ('199805','PART01','B',4100);
INSERT INTO TEST43 VALUES ('199805','PART01','C',3900);
INSERT INTO TEST43 VALUES ('199806','PART01','B',4000);
INSERT INTO TEST43 VALUES ('199806','PART01','C',3800);
INSERT INTO TEST43 VALUES ('199807','PART01','B',2400);
INSERT INTO TEST43 VALUES ('199807','PART01','C',4500);
INSERT INTO TEST43 VALUES ('199808','PART01','B',4300);
INSERT INTO TEST43 VALUES ('199808','PART01','C',1200);
INSERT INTO TEST43 VALUES ('199809','PART01','B',5600);
INSERT INTO TEST43 VALUES ('199809','PART01','C',2300);
INSERT INTO TEST43 VALUES ('199810','PART01','B',4300);
INSERT INTO TEST43 VALUES ('199810','PART01','C',3300);
INSERT INTO TEST43 VALUES ('199811','PART01','B',7300);
INSERT INTO TEST43 VALUES ('199811','PART01','C',3800);

REPORT 양식 맞추기II



건.
    TABLE : TEST43

    열 이름    유형
    ---------- ----
    YMD        VARCHAR2(6)
    ITEM_CD    VARCHAR2(6)
    TYPE_CD    VARCHAR2(1)
    AMT        NUMBER

    다음의 DATA는 1998년의 DATA중
    YMD는 1월부터 11월까지이며 ITEM_CD = 'PART1' 인 부분만 있다.
    
    YMD    ITEM_C T AMT      
    ------ ------ - ----------
    199801 PART01 B       4300
    199801 PART01 C       3300
    199802 PART01 B       4200
    199802 PART01 C       3500
    199803 PART01 B       5300
    199803 PART01 C       2300
    199804 PART01 B       2500
    199804 PART01 C       3300
    199805 PART01 B       4100
    199805 PART01 C       3900
    199806 PART01 B       4000
    199806 PART01 C       3800
    199807 PART01 B       2400
    199807 PART01 C       4500
    199808 PART01 B       4300
    199808 PART01 C       1200
    199809 PART01 B       5600
    199809 PART01 C       2300
    199810 PART01 B       4300
    199810 PART01 C       3300
    199811 PART01 B       7300
    199811 PART01 C       3800

    매월별로 각 ITEM_CD 에대해서 TYPE이 'B'/'C' 로 나뉘어져 금액이
    보관되어 있는 형식이다
    
문제.
    DATA를 보여주기 위한 형식의 하나로
    첫번째월부터  7번째월까지의 DATA는 왼편에
    여덟번째월부터 12번째월까지는 오른편에 위치시키고,
    합계를  오른편 마지막 줄에 보여준다.
    이때의 사전 조건은 최대 12개월분의 DATA만을 가져오며
    한가지 ITEM에 대해서만 출력이 이루어진다는 것이다.
    위의 샘플을 이용했을 경우 결과가 아래와 같이 나오면 된다.

    YMD        BAMT     CAMT       YMD        BAMT       CAMT      
    ---------- -------- ---------- ---------- ---------- ----------
    1998.01        4300       3300 1998.08          4300       1200
    1998.02        4200       3500 1998.09          5600       2300
    1998.03        5300       2300 1998.10          4300       3300
    1998.04        2500       3300 1998.11          7300       3800
    1998.05        4100       3900                                
    1998.06        4000       3800                                
    1998.07        2400       4500 합계            48300      35200

생각.
    REPORT양식 맞추기의 기본은 항상 각 DATA가 위치할 ROW와 COLUMN을
    지정해 주는 것이다.
    위의 DATA는 11개월분이므로 각 YMD 별로 모으면 합계까지 12개의
    ROW가 만들어진다.
    각 ROW를 YMD 순서대로 1번째부터 7번째는 왼쪽에 세우고,
    8번째부터 12번째는 오른쪽에 세운다.
    이번에는 ROW를 지정해 준다. 이때 방법은 다음과 같다.
    1번째부터 7번째 DATA는 자기의 순서를 그대로 ROW값으로 갖게하고,
    8번부터 마지막 DATA 까지는 자기번호에서 7을 뺀 값을
    ROW 값으로 갖게하며,합계의 ROW값은 14 가 되도록 한다.
    그렇게 한후 ROW값을 기준으로 GROUP BY 를 하며,각 COLUMN에
    MAX 또는 MIN 함수를 취해준다.

해법.
    단계1.제일먼저 할일은 주어진 ITEM과 주어진 기간내에 들어있는
          DATA를 읽어서
          TYPE_CD 별로 나뉘어진 ROW를 하나의 ROW로 통합하는 일이다.
          이때 합계도 함께 구한다.
          결국 예제 DATA 와 같은 경우 1월부터 11월까지의 ROW와
          합계 ROW를 합쳐 12개의 ROW가 생성된다.
  
          즉, 다음과 같은 결과를 뽑아내고자 하는것이 1차 목표이다.

          YMD           B_AMT      C_AMT    
          ------------- ---------- ----------
          1998.01             4300       3300
          1998.02             4200       3500
          1998.03             5300       2300
          1998.04             2500       3300
          1998.05             4100       3900
          1998.06             4000       3800
          1998.07             2400       4500
          1998.08             4300       1200
          1998.09             5600       2300
          1998.10             4300       3300
          1998.11             7300       3800
          합계               48300      35200

          QUERY를 다음과 같이 구성해 보자.

          SELECT DECODE(NO,2,'합계',SUBSTR(YMD,1,4)||'.'||
                                    SUBSTR(YMD,5,2)) YMD,
                 SUM(DECODE(TYPE_CD,'B',AMT)) B_AMT,
                 SUM(DECODE(TYPE_CD,'C',AMT)) C_AMT
          FROM   TEST43,
                (SELECT ROWNUM NO FROM USER_TABLES
                 WHERE  ROWNUM <=2)
          WHERE  YMD LIKE '1998%'
          AND    ITEM_CD = 'PART01'
          GROUP BY
                 DECODE(NO,2,'합계',SUBSTR(YMD,1,4)||'.'||
                                    SUBSTR(YMD,5,2))

          잠간 설명을 하자면
          ROW형식을 COLUMN형식으로 바꾸기의 전형적인 방법과
          합계구하기의 전형적인 방법이 동시에 사용되었다.
          QUERY되어온 결과를 이해하기 어려운 사람은
          이 QUERY의 바로 전단계를 생각해 보면 될 것이다.

          SELECT DECODE(NO,2,'합계',SUBSTR(YMD,1,4)||'.'||
                                    SUBSTR(YMD,5,2)) YMD,
                 DECODE(TYPE_CD,'B',AMT) B_AMT,
                 DECODE(TYPE_CD,'C',AMT) C_AMT
          FROM   TEST43,
                (SELECT ROWNUM NO FROM USER_TABLES
                 WHERE  ROWNUM <=2)
          WHERE  YMD LIKE '1998%'
          AND    ITEM_CD = 'PART01'          
          
          바로위의 QUERY는 이번단계에서 제시하는 QUERY를  GROUP BY
          하기전의 결과를 보여줄것이다.
          직접 문장을 실행 시켜보면 훨씬 이해에 도움이 될 것이다.

    단계2.이번단계는 단계1의 결과에 번호를 달아주는일이다.
          이미 GROUP BY 를 통해 YMD 순서대로 SORT 가 일어난
          상태이므로 ROWNUM을 순서로 취하기만 하면 된다.
          단 YMD가 '합계'인 경우만 14로 SETTING을 해 준다.
          이번단계의 목표는 다음과 같은 결과를 만들어 내는 것이다.

          RNO        YMD           B_AMT      C_AMT    
          ---------- ------------- ---------- ----------
                   1 1998.01             4300       3300
                   2 1998.02             4200       3500
                   3 1998.03             5300       2300
                   4 1998.04             2500       3300
                   5 1998.05             4100       3900
                   6 1998.06             4000       3800
                   7 1998.07             2400       4500
                   8 1998.08             4300       1200
                   9 1998.09             5600       2300
                  10 1998.10             4300       3300
                  11 1998.11             7300       3800
                  14 합계               48300      35200
  
          QUERY는 아래와 같이 구성될 것이다.
        
           SELECT DECODE(YMD,'합계',14,ROWNUM) RNO,
                  YMD,B_AMT,C_AMT
           FROM ( SELECT DECODE(NO,2,'합계',SUBSTR(YMD,1,4)||'.'||
                                            SUBSTR(YMD,5,2)) YMD,
                         SUM(DECODE(TYPE_CD,'B',AMT)) B_AMT,
                         SUM(DECODE(TYPE_CD,'C',AMT)) C_AMT
                  FROM   TEST43,
                        (SELECT ROWNUM NO FROM USER_TABLES
                         WHERE  ROWNUM <=2)
                  WHERE  YMD LIKE '1998%'
                  AND    ITEM_CD = 'PART01'
                  GROUP BY
                         DECODE(NO,2,'합계',SUBSTR(YMD,1,4)||'.'||
                                            SUBSTR(YMD,5,2))
                 )

          크게 어려운 부분은 없는듯 하다.
    단계3.이번단계는 최종단계로써 단계2까지의 결과를 이용해
          첫번째월부터 일곱번째 월까지를 왼쪽에 세우고
          여덟번째 월부터 마지막월까지와 합계월을 오른쪽에 세우는
          과정이 있으며,
          8번째 월부터 합계까지의 ROW 번호에서 7을 빼서 ROW값을
          만들어 준후
          그 값으로 GROUP BY 하는 과정이 있다.
          이때 각 COLUMN은 MAX 함수를 취한다.  
          먼저 왼쪽과 오른쪽으로 월을 나누는 방법을 살펴보자.
          다음과 같은 SELECT 문장이 단계2까지의 결과를 DATASET으로
          이용하여 구성된다면 어떻게 될것인지를 보자.

          DECODE(CEIL(RNO/7),1,YMD)

          각 ROW가 가진 RNO 를 7로 나눈후 그 값보다 큰 최소정수를
          취하면
          1부터  7까지는 1을 RETURN 하게 되며,
          8부터 14까지는 2를 RETURN 하게 된다.
          위의 문장은 그 RETURN 값이 1인 경우만 YMD를
          보여주겠단 의도다.
          즉 첫번째 COLUMN에 나올 DATA이다.
          똑같은 방식으로 B_AMT 와 C_AMT를 2번째와 3번째 COLUMN에
          위치시키고,
          비교값을 2로 바꾼후 YMD,B_AMT,C_AMT를 차례로 3,4,5번째
          COLUMN에 위치 시킨다.
          그리고 GROUP BY 를 위하여 하나의 COLUMN을 더 만들어 보자.
          다음을 보자.
  
          DECODE(MOD(RNO,7),0,7,MOD(RNO,7))

          SELECT 절에 추가될 내용이다.
          RNO를 7로나눈 나머지를 취하되 그 결과가 0이면 7로 바꿔주겠단
          의도이다.
          각 RNO 가 어떻게 바뀌겠는가 생각해 보자.
          1부터 7까지는 자기 자신의 값이 그대로 나오며,
          8부터 14까지는 자신의 값에서 7을 뺀 값이 나온다.
          다음과 같이 SELECT 가 구성될 것이다.
          FROM 절에는 단계2의 문장을 INLINE VIEW로 사용한다.

          SELECT DECODE(MOD(RNO,7),0,7,MOD(RNO,7)) NO,
                 DECODE(CEIL(RNO/7),1,YMD)   YMD,
                 DECODE(CEIL(RNO/7),1,B_AMT) BAMT,
                 DECODE(CEIL(RNO/7),1,C_AMT) CAMT,
                 DECODE(CEIL(RNO/7),2,YMD)   YMD,
                 DECODE(CEIL(RNO/7),2,B_AMT) BAMT,
                 DECODE(CEIL(RNO/7),2,C_AMT) CAMT

          여기까지의 중간 결과를 보자.

          NO   YMD        BAMT    CAMT    YMD       BAMT    CAMT      
          ---- ---------- ------- ------- --------- ------- -------
             1 1998.01       4300    3300
             2 1998.02       4200    3500
             3 1998.03       5300    2300
             4 1998.04       2500    3300
             5 1998.05       4100    3900
             6 1998.06       4000    3800
             7 1998.07       2400    4500
             1                              1998.08    4300    1200
             2                              1998.09    5600    2300
             3                              1998.10    4300    3300
             4                              1998.11    7300    3800
             7                              합계      48300   35200

          여기까지 DATA가 나왔다.
          이제 무엇을 해주면 되겠는가?.
          NO COLUMN을 자세히보자.
          NO가 같은 ROW를 한줄에 놓는다면 우리가 원하는
          최종결과가 된다.
          NO를 기준으로 GROUP BY 를 하되 각 COLUMN의 MAX 또는 MIN 값을
          취하면 된다.
          그리고 SELECT 에서 NO 를 가져오는 부분만 살짝 뺀다.
          최종해답은 아래와 같다.

        SELECT MAX(DECODE(CEIL(RNO/7),1,YMD))   YMD,
               MAX(DECODE(CEIL(RNO/7),1,B_AMT)) BAMT,
               MAX(DECODE(CEIL(RNO/7),1,C_AMT)) CAMT,
               MAX(DECODE(CEIL(RNO/7),2,YMD))   YMD,
               MAX(DECODE(CEIL(RNO/7),2,B_AMT)) BAMT,
               MAX(DECODE(CEIL(RNO/7),2,C_AMT)) CAMT
        FROM ( SELECT DECODE(YMD,'합계',14,ROWNUM) RNO,
                      YMD,B_AMT,C_AMT
               FROM (SELECT DECODE(NO,2,'합계',SUBSTR(YMD,1,4)||'.'||
                                               SUBSTR(YMD,5,2)) YMD,
                            SUM(DECODE(TYPE_CD,'B',AMT)) B_AMT,
                            SUM(DECODE(TYPE_CD,'C',AMT)) C_AMT
                     FROM   TEST43,
                           (SELECT ROWNUM NO FROM USER_TABLES
                            WHERE  ROWNUM <=2)
                     WHERE  YMD LIKE '1998%'
                     AND    ITEM_CD = 'PART01'
                     GROUP BY
                            DECODE(NO,2,'합계',SUBSTR(YMD,1,4)||'.'||
                                               SUBSTR(YMD,5,2))
                    )
             ) T1
        GROUP BY
               DECODE(MOD(RNO,7),0,7,MOD(RNO,7))