DEUXIGN' [émpti-spéis]

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

쉽게말해

결과물 테이블을 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

네이버 지식인에서 펌.

후에 다시 재정리