농부지기

-- 정의   : PIVOT()함수를 사용할때 컬럼갯수를 알아야만 한다.
--          결과 컬럼을 정의해 주지 않으면 PIVOT()함수를 사용할 수 없다.
--          
--          월갯수(from~to)를 알수 없을 경우에는 PIVOT()함수를 사용할 없게 된다.
--          
--          그런데, Dynamic SQL 문장을 사용하게 되면 SQL 문장을 작성할 때는 컬럼을 정의하지 않고
--          PIVOT()함수 정의 된 SQL 문장을 실행하기 전에  
--          월 갯수를 파악하여  PIVOT()함수에 추가한다.
--          (즉, myBatis 에서  FOR PLAN_YM IN ($COLUMN_LIST$)  와 같이 함)

SELECT (CASE WHEN GRP_ID   = '0' THEN PLAN_YM
             WHEN MM_COUNT <  6  THEN '소계'
             WHEN MM_COUNT =  6 AND FIRST_SECOND_TYPE = '0' THEN '상반기'
             WHEN MM_COUNT =  6 AND FIRST_SECOND_TYPE = '1' THEN '하반기'
             WHEN MM_COUNT = 12                             THEN '년계'
             ELSE '총계' END) AS PLAN_YM
     , FIRST_SECOND_TYPE         --0:상반기,  1:하반기
     , MM_COUNT                  --1:Table 컬럼 sum()값.  2이상:소계, 합계 컬럼임
     , GRP_ID                    --GROUPING_ID()함수 결과 값.
     , COL_ROWNUM                --컬럼 ROWNUM
  FROM (
        SELECT YEAR_DATA
             , PLAN_YM
             , FIRST_SECOND_TYPE
             , COUNT(FIRST_SECOND_TYPE) AS MM_COUNT
             , GROUPING_ID(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM) AS GRP_ID
          FROM (
                --2013.10 ~ 201502 월까지 존재 하는 테이블일 경우
                WITH YY_MM AS
                    ( SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201310', 'YYYYMM'), ROWNUM), 'YYYYMM') AS PLAN_YM
                        FROM DUAL
                      CONNECT BY LEVEL <= 17 )
                SELECT SUBSTR(B.PLAN_YM, 1,4)                   AS YEAR_DATA
                     , B.PLAN_YM                                AS PLAN_YM
                     , FLOOR ((SUBSTR(B.PLAN_YM,5,2) - 1) / 6)  AS FIRST_SECOND_TYPE
                  FROM YY_MM B
               )
        GROUP BY ROLLUP(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM)
       )
 WHERE GRP_ID IN ('0', '1', '7') 
    OR (MM_COUNT = 12 AND GRP_ID = 3) ;

-- 정의   : 반복 컬럼이 1 ~ 3개 일 경우
--          PIVOT 함수 사용중  시작년월~종료년월 까지 자료를 조회 할 경우
-- 사용처 : PIVOT 조회결과를 화면에 보여 주려면  화면.Grid.Header를 dynamic하게 그려줘야 된다.
--          이때 고정컬럼을 항상 정해졌기 때문에 문제가 없고
--          반복컬럼과 중간에 소계, 합계가 들어 갈 경우 
--          아래 SQL문 결과를 통해서 Grid.Header를 그려줄 수 있다.

SELECT (CASE WHEN GRP_ID   = '0' THEN PLAN_YM
             WHEN MM_COUNT <  6  THEN '소계'
             WHEN MM_COUNT =  6 AND FIRST_SECOND_TYPE = '0' THEN '상반기'
             WHEN MM_COUNT =  6 AND FIRST_SECOND_TYPE = '1' THEN '하반기'
             WHEN MM_COUNT = 12                             THEN '년계'
             ELSE '총계' END) AS PLAN_YM
       --실 컬럼명은 변수로 받아야 됨
     , DECODE(COL_GUBN_GRP, 1, '비중', 2, '매출액', 3, '이익금') AS COL_NAME
     , FIRST_SECOND_TYPE         --0:상반기,  1:하반기
     , MM_COUNT                  --1:Table 컬럼 sum()값.  2이상:소계, 합계 컬럼임
     , GRP_ID                    --GROUPING_ID()함수 결과 값.
     , COL_ROWNUM                --컬럼 ROWNUM
     , COL_GUBN_GRP              --반복컬럼이 1개 이상일 경우 순서
  FROM (
        WITH YY_MM AS
           (
            SELECT YEAR_DATA
                 , PLAN_YM
                 , FIRST_SECOND_TYPE
                 , COUNT(FIRST_SECOND_TYPE) AS MM_COUNT
                 , GROUPING_ID(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM) AS GRP_ID
              FROM (SELECT SUBSTR(A.PLAN_YM, 1,4)                     AS  YEAR_DATA
                         , A.PLAN_YM                                  AS PLAN_YM
                         , FLOOR ((SUBSTR(A.PLAN_YM,5,2) - 1) / 6)    AS  FIRST_SECOND_TYPE
                      FROM ( --2013.10 ~ 201502 월까지 존재 하는 테이블
                             SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201310', 'YYYYMM'), ROWNUM), 'YYYYMM') AS PLAN_YM
                               FROM DUAL
                            CONNECT BY LEVEL <= 17 
                           ) A
                   )
            GROUP BY ROLLUP(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM)
           )
        SELECT PLAN_YM
             , FIRST_SECOND_TYPE
             , MM_COUNT
             , GRP_ID
             --** 맨 앞쪽  1은  변수로 받아야 됨. (반복컬럼 갯수 임)
             , DECODE(3, 1, ROWNUM, 2, (ROWNUM * 2) - 1, (ROWNUM * 3) - 2) AS COL_ROWNUM   
             , 1      AS COL_GUBN_GRP
          FROM  YY_MM
         WHERE  GRP_ID IN ('0', '1', '7') OR (MM_COUNT = 12 AND GRP_ID = 3)

        UNION ALL

        SELECT PLAN_YM
             , FIRST_SECOND_TYPE
             , MM_COUNT
             , GRP_ID
             --** 맨 앞쪽  2는  변수로 받아야 됨. (반복컬럼 갯수 임)
             , DECODE(3, 2, (ROWNUM * 2) - 0, (ROWNUM * 3) - 1) AS COL_ROWNUM  
             , 2      AS COL_GUBN_GRP
          FROM  YY_MM
         WHERE ( GRP_ID IN ('0', '1', '7') OR (MM_COUNT = 12 AND GRP_ID = 3) ) 
           AND 2 <= 2    --** 오른쪽  2는  변수로 받아야 됨. (반복컬럼 갯수 임)

       UNION ALL

        SELECT PLAN_YM
             , FIRST_SECOND_TYPE
             , MM_COUNT
             , GRP_ID
             , (ROWNUM * 3) - 0 AS COL_ROWNUM  
             , 3                AS COL_GUBN_GRP
          FROM  YY_MM
         WHERE ( GRP_ID IN ('0', '1', '7') OR (MM_COUNT = 12 AND GRP_ID = 3) ) 
           AND 3 = 3    --** 오른쪽  3은  변수로 받아야 됨. (반복컬럼 갯수 임)
       )
 ORDER BY COL_ROWNUM ;