TABLE :

CREATE TABLE SUB_SUM (NO1         NUMBER PRIMARY KEY,
                      UNCASH_DATE VARCHAR2(08) NOT NULL,
                      SO_GUBUN    VARCHAR2(01),
                      BILL_AMT    NUMBER,
                      CUST_NAME   VARCHAR2(10));

문제  : SUB_SUM 테이블에 다음과 같은 DATA가 있습니다.
        실제 상황을 간략화 하여 하나의 예제 테이블을 만들었습니다.
        NO1 COLUMN은 해당 결과의 ROWNUM일수도 있습니다.
        아래와 같은 DATA를 이용해

        NO1        UNCASH_D S BILL_AMT   CUST_NAME
        ---------- -------- - ---------- ----------
                 1 19990601 1        100 TEST1      
                 2 19990601 2        200 TEST2    
                 3 19990601 1        300 TEST3    
                 4 19990601 2        400 TEST4    
                 5 19990602 1        500 TEST5    
                 6 19990602 1        600 TEST6    
                 7 19990602 1        700 TEST7    
                 8 19990603 2        800 TEST8    
                 9 19990603 2        900 TEST9

        다음과 같은 결과를 보고 싶습니다.

        KEY1     K AMT        CUST      
        -------- - ---------- ----------
        19990601 1        100 TEST1    
        19990601 2        200 TEST2    
        19990601 1        300 TEST3    
        19990601 2        400 TEST4    
        1  계             400          
        2  계             600          
        합 계            1000          
        19990602 1        500 TEST5    
        19990602 1        600 TEST6    
        19990602 1        700 TEST7    
        1  계            1800          
        2  계                          
        합 계            1800          
        19990603 2        800 TEST8    
        19990603 2        900 TEST9    
        1  계                          
        2  계            1700          
        합 계            1700          
        총 1  계         2200          
        총 2  계         2300          
        총 합 계         4500

        UNCASH_DATE 가 바뀌는 시점에서 SO_GUBUN 이 1인것과 2인것의
        소계를 한줄씩 보여주고 일자별 합계를 보여주며,
        마지막에 전체에 대한 1과2의 각 총계와 전체총계를
        보여주는 것입니다.

해법 :
        SELECT MAX(DECODE(CNO,1,DT1,2,'1  계',
                          3,'2  계',
                          4,'합 계',
                          5,'총'||' '||'1  계',
                          6,'총'||' '||'2  계',
                          7,'총'||' '||'합 계')) KEY1,
               DECODE(CNO,1,SO_GUBUN) KEY2,
               SUM(DECODE(CNO,1,BILL_AMT,
                          2,DECODE(SO_GUBUN,1,BILL_AMT),
                          3,DECODE(SO_GUBUN,2,BILL_AMT),
                          4,BILL_AMT,
                          5,DECODE(SO_GUBUN,1,BILL_AMT),
                          6,DECODE(SO_GUBUN,2,BILL_AMT),
                          7,BILL_AMT)) AMT,
               MAX(DECODE(CNO,1,CUST_NAME)) CUST
        FROM  (SELECT NO1,NO2,
                      A.UNCASH_DATE DT1,NVL(B.UNCASH_DATE,'~') DT2,
                      A.SO_GUBUN,A.BILL_AMT,A.CUST_NAME
               FROM  (SELECT  NO1,
                              UNCASH_DATE,
                              SO_GUBUN,

                              BILL_AMT,
                              CUST_NAME
                      FROM    SUB_SUM
                      WHERE   NO1 < 10
                     ) A,
                     (SELECT  NO1 NO2,
                              UNCASH_DATE
                      FROM    SUB_SUM
                      WHERE   NO1 < 10
                     ) B
               WHERE  B.NO2(+) = A.NO1+1) A1,
              (SELECT ROWNUM CNO FROM USER_TABLES WHERE ROWNUM<=7)  B1
        GROUP BY DECODE(CNO,1,DT1,
                          2,DT1||' '||'1  계',
                          3,DT1||' '||'2  계',
                          4,DT1||' '||'합 계',
                          5,'총'||' '||'1  계',
                          6,'총'||' '||'2  계',
                          7,'총'||' '||'합 계'),
                 DECODE(CNO,1,NO1),DECODE(CNO,1,SO_GUBUN)