CREATE TABLE TEST36 (PLANYY VARCHAR2(04),KEY1 VARCHAR2(06),
                     PLANMM VARCHAR2(50),
                     SEQ NUMBER,AMT NUMBER);
CREATE UNIQUE INDEX TEST36_I ON TEST36(PLANYY,KEY1);
INSERT INTO TEST36 VALUES ('1999','A0001','1월'||','||'2월',2,3000);
INSERT INTO TEST36
            VALUES ('1999','A0002','4월'||','||'7월'||','||'11월',3,3000);
INSERT INTO TEST36 VALUES ('1999','A0003','5월',3,3000);
INSERT INTO TEST36 VALUES ('1999','A0004','8월'||','||'9월',2,8000);
INSERT INTO TEST36 VALUES ('1999','A0005','09월'||','||'12월',2,2000);
INSERT INTO TEST36 VALUES ('1999','A0006','1월'||','||'2월'||','||'5월'
                                          ||','||'7월',4,3000);
INSERT INTO TEST36 VALUES ('1999','A0007','10월',1,5000);



DATA 변환



조건.
    TEST36이 아래와 같은 구조의 DATA를 가지고 있다.

    PLANYR KEY1   PLANMM             SEQ        AMT
    ------ ------ ------------------ ---------- ----------
    1999   A0001  1월,2월                     3       3000
    1999   A0002  4월,7월,11월                3       3000
    1999   A0003  5월                         3       3000
    1999   A0004  8월,9월                     2       8000
    1999   A0005  09월,12월                   2       2000
    1999   A0006  1월,2월,5월,7월             4       3000
    1999   A0007  10월                        1       5000

문제.
    PLANMM 에는 1월부터 12월까지의 DATA중 몇개일지 모르는 해당월들이
    위와 같은 형식으로 ','구분자를 가지고 입력되어 있다.
    규칙이 있다면 매월뒤에 '월'이라는 문자가 공백없이 붙는다는 것과
    매월의 구분자가 ','라는 것이다.
    1월은 '1월' 과 같이 입력될수도 있고 '01월' 과 같이 입력될
    수 도 있다.
    위의 DATA 를 변환하여 아래와같은 형식으로 바꾸고자 한다.

    PLAN KEY1   MON SEQ        AMT      
    ---- ------ --- ---------- ----------
    1999 A0001  01           3       3000
    1999 A0001  02           3       3000
    1999 A0002  04           3       3000
    1999 A0002  07           3       3000
    1999 A0002  11           3       3000
    1999 A0003  05           3       3000
    1999 A0004  08           2       8000
    1999 A0004  09           2       8000
    1999 A0005  09           2       2000
    1999 A0005  12           2       2000
    1999 A0006  01           4       3000
    1999 A0006  02           4       3000
    1999 A0006  05           4       3000
    1999 A0006  07           4       3000
    1999 A0007  10           1       5000

    DATA 전환 방식이야 여러가지가 있겠지만 SQL 한 문장으로
    만들어보자.
생각.
    이 문제를 통해 얘기하고자 하는 요점은 하나다.
    DATA의 구조가 원하는 형식을 이끌어 내기에 어려운 형태로
    존재한다 하여도 일정한 규칙성을 가지고 있으면 가공을 통해
    SQL 안으로 끌어들일 수 있다는 것이다.
    위의 문제를 하나의 SQL이 아닌 일반 APPLICATION PROGRAM 이나
    PL/SQL을 통해서 처리하라면 쉽게 해결할 수 있을 것이다.
    마찬가지로 SQL 안으로 끌어들여 한문장으로 만들려면 가장먼저
    규칙성을 찾아야 한다.

해법.
    SEQ 와 AMT COLUMN은 접어두고 PLANMM 을 보자.
    1월 부터 12월까지 12개가 있을 수도 있고, 12개월 중 한달만 있을
    수 도있다.
    문제에서도 언급 되었듯이 월을 나타내는 숫자 뒤에는 공백없이
    '월' 이라는 문자가 따라 붙으며 각 월 뒤에는 ',' 로 구분이
    이루어진다.
    그 규칙에 위배되어 입력이 이루어진 DATA는 무시해도
    좋다는 얘기다.
    조건을 위배하는 DATA가 없다고 가정하면 위의 두가지 사항이 문제를
    풀어가는 열쇠인 규칙이 된다.
    그 전에, 결과로 나온 DATA가 매월을 하나의 ROW로 나타내고 있다.
    COLUMN을 ROW로 만드는 방식을 이용하여 최종 결과를 유도 한다고하면
    12개월을 염두에 둔 12번의 복제가 이루어져야한다.
    COLUMN을 ROW 형식으로 바꾸는 방식은 여러번 다루었기에 여기에서는
    더이상 언급하지 않겠다.
    첫번째 복제가 이루어진 DATA는 1월을 위해 사용하고,두번째는 2월,
    세번째는 3월 과 같은 식으로 12번째는 12월을 위해서 사용한다.
    단계1.다음문장을 보자.

          SELECT PLANYY,KEY1,
                 PLANMM,RNUM
          FROM   TEST36,
                (SELECT ROWNUM RNUM
                 FROM   USER_TABLES WHERE ROWNUM < 13)
          ORDER BY 1,2,4
          이렇게 하면 TEST36 의 DATA가 한 RECORD 마다 12번씩
          복제가 일어나 12 개의 번호표를 달고 12개의 ROW로 만들어진다.
          첫번째 RECORD만 참고로 보자.

    PLAN KEY1   PLANMM              RNUM
    ---- ------ ------------------- ----------
    1999 A0001  1월,2월                      1
    1999 A0001  1월,2월                      2
    1999 A0001  1월,2월                      3
    1999 A0001  1월,2월                      4
    1999 A0001  1월,2월                      5
    1999 A0001  1월,2월                      6
    1999 A0001  1월,2월                      7
    1999 A0001  1월,2월                      8
    1999 A0001  1월,2월                      9
    1999 A0001  1월,2월                     10
    1999 A0001  1월,2월                     11
    1999 A0001  1월,2월                     12

    단계2.위의 결과를 놓고 다시한번 생각해 보자. PLANMM 에서
          '월' 이라는 문자가 RNUM 번째 나타나는 자리가 어찌
          되겠는가?.
          1번째는 3 STRING BYTE 에 2번째는 6 STRING BYTE에 나타난다.
          나머지는 어떻겠는가?.
          단계1에서 예로 보여준 DATA의 경우는 3번째 '월' 이 나타나는
          위치는 없다.
          INSTRB라는 함수를 써서 구해보자. 단계1의 SELECT 절에
          "INSTRB(PLANMM,'월',1,RNUM)" 를 추가하면 다음과 같은
          결과가 나온다.

    PLAN KEY1   PLANMM                RNUM       INSTRB(PLA
    ---- ------ --------------------- ---------- ----------
    1999 A0001  1월,2월                        1          2
    1999 A0001  1월,2월                        2          6
    1999 A0001  1월,2월                        3          0
    1999 A0001  1월,2월                        4          0
    1999 A0001  1월,2월                        5          0
    1999 A0001  1월,2월                        6          0
    1999 A0001  1월,2월                        7          0
    1999 A0001  1월,2월                        8          0
    1999 A0001  1월,2월                        9          0
    1999 A0001  1월,2월                       10          0
    1999 A0001  1월,2월                       11          0
    1999 A0001  1월,2월                       12          0          
        
          위의 경우는 첫번째 두번째를 빼고 모두 영 이 나왔다.
          그렇다면 함수를 적용한 결과가 영 이나온 DATA는 조건절을
          이용해 걸러내 보자.

          SELECT PLANYY,KEY1,
                 PLANMM,RNUM,INSTRB(PLANMM,'월',1,RNUM)
                FROM   TEST36,
                      (SELECT ROWNUM RNUM
                       FROM   USER_TABLES WHERE ROWNUM < 13)
                WHERE  INSTRB(PLANMM,'월',1,RNUM) > 0
          ORDER BY 1,2,4
    
          전체 결과는 아래와 같다.

    PLAN KEY1   PLANMM           RNUM   INSTRB(PLA
    ---- ------ ---------------- ------ ----------
    1999 A0001  1월,2월               1          2 - <== 12개가
    1999 A0001  1월,2월               2          6 - 두개만 남는다
    1999 A0002  4월,7월,11월          1          2
    1999 A0002  4월,7월,11월          2          6
    1999 A0002  4월,7월,11월          3         11
    1999 A0003  5월                   1          2
    1999 A0004  8월,9월               1          2
    1999 A0004  8월,9월               2          6
    1999 A0005  09월,12월             1          3
    1999 A0005  09월,12월             2          8
    1999 A0006  1월,2월,5월,7월       1          2
    1999 A0006  1월,2월,5월,7월       2          6
    1999 A0006  1월,2월,5월,7월       3         10
    1999 A0006  1월,2월,5월,7월       4         14
    1999 A0007  10월                  1          3

    PLANMM 에 들어있는 '월' 이라는 문자만큼 RECORD가 생겼으며
    '월'이라는 문자가 위치한 자리까지를 구해냈다.
    이러한 정보를 이용하면 처음보다는 접근하기가 수월해 졌다는
    생각이 들 것이다.
    단계3.이제 위의 정보를 이용해 '월' 이라는 문자가 나타난 위치를
          기준으로 앞의 두 문자를  읽어와 보자.
          단계2의 위치정보를 읽는 부분을 다음처럼 바꿔주면 된다.
          "SUBSTRB(PLANMM,INSTRB(PLANMM,'월',1,RNUM) - 2,2)"
          결과는 이렇다.

    PLAN KEY1   PLANMM               RNUM          SU
    ---- ------ -------------------- ----------    --
    1999 A0001  1월,2월                       1    1
    1999 A0001  1월,2월                       2    ,2
    1999 A0002  4월,7월,11월                  1    4
    1999 A0002  4월,7월,11월                  2    ,7
    1999 A0002  4월,7월,11월                  3    11
    1999 A0003  5월                           1    5
    1999 A0004  8월,9월                       1    8
    1999 A0004  8월,9월                       2    ,9
    1999 A0005  09월,12월                     1    09
    1999 A0005  09월,12월                     2    12
    1999 A0006  1월,2월,5월,7월               1    1
    1999 A0006  1월,2월,5월,7월               2    ,2
    1999 A0006  1월,2월,5월,7월               3    ,5
    1999 A0006  1월,2월,5월,7월               4    ,7
    1999 A0007  10월                          1    10  
    단계4.위의 마지막 FIELD를 다시 가공하자.
          SPACE가 있을지 모르니 SPACE를 NULL로 치환한다.
          REPLACE 함수를 쓰면 된다.
          다음으로 LPAD 함수를 이용해 좌측에 '0' 문자를 채워 넣는다.

          LPAD(REPLACE(SUBSTRB(PLANMM,INSTRB(PLANMM,'월',1,RNUM)-2,2
                                 ),' ',NULL
                         ),2,'0'
                 )
          위와같이 기술하면 된다.
          결과는 단계3의 정보에서 앞에 ',' 가 없는 것은 '01','02',
          와 같이 바뀌어 나오고 '1' 문자나 ',' 문자가 있는 것은
          그대로 나온다.
          여기까지의 정보를 INLINE VIEW 로 이용하자.
          반드시 그럴 필요는 없지만 INLINE VIEW를 사용하면 문장도
          깔끔해지고, 한눈에 이해하기가 수월해 진다.
          그리고 계속 같은 COLUMN 정보를 가공한다.
          2개의 문자로 나눠서
          앞의 문자가 ',' 이면 '0' 으로 치환해 주고 아니면 그냥
          이용한다.
          그리고 나눠진 문자를 다시 붙여보자.
          단계3의 정보 ALIAS 를 MON 이라 부여했다고 가정하면
          다음과 같이 가공하면 된다.
          "DECODE(SUBSTR(MON,1,1),'1','1','0')||SUBSTR(MON,2,1)"
          그리고 필요한 정보도 같이 가져온다.
          최종 문장은 아래와 같이 된다.
    
          SELECT PLANYY,KEY1,
                 DECODE(SUBSTR(MON,1,1),'1','1','0')||
                 SUBSTR(MON,2,1) MON,
                 SEQ,AMT
          FROM  (SELECT PLANYY,KEY1,
                        LPAD(REPLACE(SUBSTRB(PLANMM,
                                     INSTRB(PLANMM,'월',1,RNUM) - 2,2
                                            ),' ',NULL
                                    ),2,'0'
                            ) MON,
                        SEQ,AMT
                 FROM   TEST36,
                       (SELECT ROWNUM RNUM
                        FROM   USER_TABLES WHERE ROWNUM < 13)
                 WHERE  INSTRB(PLANMM,'월',1,RNUM) > 0
                )
          ORDER BY 1,2,3
뒷풀이.
    DATA를 SQL안에서 가공해가는 과정을 보여주고자 했다.
    PL/SQL이나 APPLICATION LANGUAGE를 이용하지 않더라도 많은 부분이
    위와 같은 형식으로 가공 가능하다.
    원하는 DATA를 한 문장의 SQL로 만드느냐 못 만드느냐가 중요한
    이유는
    더 이상 얘기 하지 않아도 모두 알고 있을 것이다.