[ORACLE] n개 ROW 데이터들을 각각의 컬럼으로 합치려는 경우..

 

최근 맞닥뜨린 문제는 아래와 같다.

MAIN_TABLE
KEY TYPE
 1  AB
 2  AB
 
SUB_TABLE_A (TYPE A의 정보를 갖고 있는 테이블)
KEY TYPE VALUE
 1  A   10
 2  A   20

SUB_TABLE_B (TYPE B의 정보를 갖고 있는 테이블)
KEY TYPE VALUE
 1  B   30
 2  B   40
 
--> 이 데이터를 아래처럼 보여주고 싶을 경우
KEY TYPE A_VALUE B_VALUE
1  AB  10 30
2  AB  20 40

먼저 A_VALUE와 B_VALUE 의 값은 정수형이다. (어쩌면 정수형이기 때문에 #1 의 방법으로 쉽게 해결이 돼서 참 다행이라 생각했었지만 #2 를 알고 난 후에는 정수든 뭐든... 이라는 생각이..)

보통 1개 이상의 레코드를 합칠때는 오라클 버전별로 아래의 함수들을 사용할 것이다.

9i : XMLAGG

10g: WM_CONCAT

11g: LISTAGG

 

하지만 위 함수들은 내가 지금 당면해 있는 문제를 해결하기에는 적절치 않았다.

하여... 사용했던 방법들은 아래와 같다.

#1. 처음에 짰던 방법....
SELECT M.KEY, M.TYPE, S.A_VALUE, 0 AS B_VALUE
FROM MAIN_TABLE M
, SUB_TABLE_A S
WHERE M.KEY = B.KEY
AND REPLACE(M.TYPE, 'B', '') = S.TYPE
AND M.TYPE = 'AB'
UNION ALL
SELECT M.KEY, M.TYPE, 0 AS S.A_VALUE, B_VALUE
FROM MAIN_TABLE M
, SUB_TABLE_B S
WHERE M.KEY = B.KEY
AND REPLACE(M.TYPE, 'A', '') = S.TYPE
AND M.TYPE = 'AB'

--> 이렇게 조회하면 아래와 같이 나올 것이다.
KEY TYPE A_VALUE B_VALUE
 1   AB    10   0
 1   AB    0   20
 2   AB    30   0
 2   AB    0   40
 
 
--> 이것을 SUM 과 PARTITION BY , GROU PBY 를 하면..
SELECT A.KEY, SUM(A_VALUE) OVER(PARTITION BY A.KEY) AS A_VALUE, SUM(B_VALUE) OVER(PARTITION BY A.KEY) AS B_VALUE
FROM (
SELECT M.KEY, M.TYPE, S.A_VALUE, 0 AS B_VALUE
FROM MAIN_TABLE M
, SUB_TABLE_A S
WHERE M.KEY = B.KEY
AND REPLACE(M.TYPE, 'B', '') = S.TYPE
AND M.TYPE = 'AB'
UNION ALL
SELECT M.KEY, M.TYPE, 0 AS S.A_VALUE, B_VALUE
FROM MAIN_TABLE M
, SUB_TABLE_B S
WHERE M.KEY = B.KEY
AND REPLACE(M.TYPE, 'A', '') = S.TYPE
AND M.TYPE = 'AB'
) A GROUP BY A.KEY, A_VALUE, B_VALUE;

--> 결과는....
KEY TYPE A_VALUE B_VALUE
 1   AB    10   20
 1   AB    10   20
 2   AB    30   40
 2   AB    40   40
 
--> 이것을 다시 GROUP BY 하게 되면...
SELECT B.KEY, B.A_VALUE, B.B_VALUE
FROM (
SELECT A.KEY, SUM(A_VALUE) OVER(PARTITION BY A.KEY) AS A_VALUE, SUM(B_VALUE) OVER(PARTITION BY A.KEY) AS B_VALUE
FROM (
SELECT M.KEY, M.TYPE, S.A_VALUE, 0 AS B_VALUE
FROM MAIN_TABLE M
, SUB_TABLE_A S
WHERE M.KEY = B.KEY
AND REPLACE(M.TYPE, 'B', '') = S.TYPE
AND M.TYPE = 'AB'
UNION ALL
SELECT M.KEY, M.TYPE, 0 AS S.A_VALUE, B_VALUE
FROM MAIN_TABLE M
, SUB_TABLE_B S
WHERE M.KEY = B.KEY
AND REPLACE(M.TYPE, 'A', '') = S.TYPE
AND M.TYPE = 'AB'
) A GROUP BY A.KEY, A_VALUE, B_VALUE
) B GROUP BY B.KEY, B.A_VALUE, B.B_VALUE

--> 결과는....
KEY TYPE A_VALUE B_VALUE
 1   AB    10   20
 2   AB    30   40

 
#2. WITH 를 사용한 방법
WITH RESULT_A AS (
SELECT M.KEY, S.A_VALUE
FROM MAIN_TABLE M
, SUB_TABLE_A S
WHERE M.KEY = B.KEY
AND REPLACE(M.TYPE, 'B', '') = S.TYPE
AND M.TYPE = 'AB'
), RESULT_B AS (
SELECT M.KEY, S.B_VALUE
FROM MAIN_TABLE M
, SUB_TABLE_B S
WHERE M.KEY = B.KEY
AND REPLACE(M.TYPE, 'A', '') = S.TYPE
AND M.TYPE = 'AB'
)
SELECT A.KEY, A.A_VALUE, B.B_VALUE
FROM RESULT_A A
, RESULT_B B
WHERE A.KEY = B.KEY;

--> 결과
KEY TYPE A_VALUE B_VALUE
 1   AB    10   20
 2   AB    30   40
 
.... WITH 를 사용하니 훨씬 간단해졌다.
만약 데이터가 두 테이블 중에 없을 수도 있다면 JOIN 으로 인해 결과가 정상적으로 나오지 않을 수도 있으므로,
OUTER JOIN 정도는 걸어 주는것도 좋겠다. (어느 테이블에서 데이터가 없는지 모른다면 FULL OUTER JOIN 을 걸던...)