영원히 잊지 않겠습니다.

티스토리 툴바

달력

012012  이전 다음

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  •  
  •  
  •  
  •  


카티션곱을 이용하면 됩니다.

 

쉽게말해

 

결과물 테이블을 A 라고 하면

 

SELECT * FROM A

 

NAME     GROUP  RESULT

-----------------------
이XX            A            10
이XX            B            20
이XX            C            15
강XX            B            10
강XX            C            20
강XX            D            30
조XX            A            40
조XX            E            20


이것을

 

NAME      A      B    C    D    E

--------------------------

이XX       10    20    15   -    -

강XX        -    10     20  30   -
조XX       40    -       -    -   20


이렇게 나오게 하면 된다는 거죠?

 

방법은 우선 그룹테이블 B를 만듭니다.

 

SELECT * FROM B

 

NO     GROUP_A

-------------------
1            A
2            B
3            C
4            D
5            E

또는

 

SELECT ROWNUM NO ,GROUP FROM
    (SELECT GROUP FROM A GROUP BY GROUP)

 

이렇게 만들어주셔도 됩니다.

 

이렇게 임시테이블을 만들었다면

복제를 합니다.

 

SELECT * FROM A,B

 

NAME   GROUP_A  RESULT     NO      GROUP_B

----------------------------------------------
이XX            A            10            1            A
이XX            B            20            1            A
이XX            C            15            1            A
강XX            B            10            1            A
강XX            C            20            1            A
강XX            D            30            1            A
조XX            A            40            1            A
조XX            E            20            1            A
이XX            A            10            2            B
이XX            B            20            2            B
이XX            C            15            2            B
강XX            B            10            2            B
강XX            C            20            2            B
강XX            D            30            2            B
조XX            A            40            2            B
조XX            E            20            2            B
이XX            A            10            3            C
이XX            B            20            3            C
이XX            C            15            3            C
강XX            B            10            3            C
강XX            C            20            3            C
강XX            D            30            3            C
조XX            A            40            3            C
조XX            E            20            3            C
이XX            A            10            4            D
이XX            B            20            4            D
이XX            C            15            4            D
강XX            B            10            4            D
강XX            C            20            4            D
강XX            D            30            4            D
조XX            A            40            4            D
조XX            E            20            4            D
이XX            A            10            5            E
이XX            B            20            5            E
이XX            C            15            5            E
강XX            B            10            5            E
강XX            C            20            5            E
강XX            D            30            5            E
조XX            A            40            5            E
조XX            E            20            5            E


A갯수 X B갯수 인 8 X 5 = 40 개가 생겼겠죠?

여기에서 NAME로 그룹바이를 해주고 디코딩 해주면 됩니다.

 

SELECT A.NAME
    , NVL(MAX( DECODE( A.GROUP, 'A', RESULT )),'-') "A"
    , NVL(MAX( DECODE( A.GROUP, 'B', RESULT )),'-') "B"
    , NVL(MAX( DECODE( A.GROUP, 'C', RESULT )),'-') "C"
    , NVL(MAX( DECODE( A.GROUP, 'D', RESULT )),'-') "D"
    , NVL(MAX( DECODE( A.GROUP, 'E', RESULT )),'-') "E"
FROM A,B
GROUP BY NAME

 

 

NAME           A            B            C            D            E

------------------------------------------------
강XX             -            10            20            30       -
이XX            10           20            15            -         -
조XX            40            -             -             -          20


이해를 돕기 위해 B테이블을 만들었는데요

 

좀 복잡하지만 간단하게할수도 있습니다

 

SELECT   DECODE( RNUM, 1, NAME ) NAME
    , NVL(MAX( DECODE( GROUP_A, 'A', RESULT )),'-') "A"
    , NVL(MAX( DECODE( GROUP_A, 'B', RESULT )),'-') "B"
    , NVL(MAX( DECODE( GROUP_A, 'C', RESULT )),'-') "C"
    , NVL(MAX( DECODE( GROUP_A, 'D', RESULT )),'-') "D"
    , NVL(MAX( DECODE( GROUP_A, 'E', RESULT )),'-') "E"
    FROM ( SELECT NAME
                , GROUP_A
                , RESULT
                , ROW_NUMBER( ) OVER( PARTITION BY NAME, GROUP_A ORDER BY ROWNUM ) RNUM
            FROM A
  )
GROUP BY NAME , RNUM
ORDER BY NAME

 

결과는 똑같습니다.

 

NAME           A            B            C            D            E

------------------------------------------------
강XX             -            10            20            30       -
이XX            10           20            15            -         -
조XX            40            -             -             -          20



네이버 지식인에서 펌.

후에 다시 재정리

Posted by 듀시즌

Trackback : http://deuxism.tistory.com/trackback/2694725 관련글 쓰기

댓글을 달아 주세요