Oracle에서 행과 열 변환하기
DECODE & Cartesian Product, PIVOT
이번 시간에는 Oracle DBMS(이하 Oracle)에서 행과 열을 변환하는 방법에 대해 살펴보겠습니다.
Oracle에서 행과 열을 변환하는 방법은 여러 가지가 있습니다. 먼저 전통적인 기법으로 집계 함수(MIN, MAX 함수 등)와 DECODE 함수를 조합하여 사용하는 방법(행을 열로 변환할 때)과 Cartesian Product를 활용하는 방법(열을 행으로 변환할 때)이 있으며, SQL:2003 표준이면서 Oracle Database 11g에서 새롭게 소개된 PIVOT과 UNPIVOT을 활용하는 방법이 있습니다.
이 글에서는 Oracle에서 제공하는 집계 함수와 DECODE 함수를 조합하여 행과 열을 변환하는 방법과 Cartesian Product를 활용하여 열을 행으로 변환하는 방법, 그리고 PIVOT과 UNPIVOT을 활용하는 방법을 소개합니다. 각 기법의 사용 방법을 정확하게 학습한 후, 알맞은 활용처에서 사용하면 복잡한 문제를 쉽게 해결할 수 있습니다.
Sample Data
이 글에서 사용하는 예제는 1~10까지의 순열을 활용하여 설명하겠습니다. 1~10까지의 순열을 생성하는 방법은 각각의 예제에 포함되어 있습니다. 이 글에서 활용하는 SQL은 가능하면 명시적 표현법을 적용하고, ANSI SQL 문법을 기준으로 작성하겠습니다. 단, Oracle에서만 활용되는 문법 또는 키워드는 Oracle 문법에 따릅니다.
About DECODE & Cartesian Product
이번 단락에서는 MIN 함수와 DECODE 함수를 사용하여 행 단위로 추출되는 연산을 열로 변경하고, Cartesian Product를 활용하여 열 단위의 자료를 행으로 변환하는 방법에 대해 살펴보겠습니다.
집계 함수와 DECODE 함수를 사용하여 행을 열로 변경하기
집계 함수와 DECODE 함수를 사용하여 행을 열로 변환하는 방법은 이미 잘 알려진 방법입니다. 간단한 예제를 이용하여 행을 열로 변환하는 방법을 살펴보겠습니다. 이 예제를 설명하기 위해 생성할 데이터는 1~10까지의 순열로서 결과적으로 10개의 행을 생성합니다.
우리에게 주어진 문제는 순열의 숫자를 홀수와 짝수로 구분하여, 요소별로 한 행에 표현하는 것입니다. 다음 질의를 수행하면, 정확하게 홀수와 짝수가 구분되어 출력되는 것을 확인할 수 있습니다. 일반적으로 활용되는 질의이며, 복잡한 질의가 아니므로 풀이과정에 대한 설명은 생략하겠습니다.
집계 함수와 DECODE 함수를 사용하여 행을 열로 변환하기
with test as (
select level no
from dual
connect by level <= 10
) select decode(mod(no,2),1,'odd','even') gr
, max(decode(round(no/2),1,no)) g1
, max(decode(round(no/2),2,no)) g2
, max(decode(round(no/2),3,no)) g3
, max(decode(round(no/2),4,no)) g4
, max(decode(round(no/2),5,no)) g5
from test
group by decode(mod(no,2),1,'odd','even');
Cartesian Product를 사용하여 열을 행으로 변경하기
Cartesian Product를 이용하여 열을 행으로 변환하는 방법도 이미 잘 알려진 방법입니다. 간단한 예제를 이용하여 변환하는 방법을 살펴보겠습니다. 이 예제에서 생성한 데이터는 앞에서 살펴본 문제의 수행 결과와 같습니다. 즉, 홀수와 짝수로 분리되어 2개의 행으로 나뉘어 출력됩니다.
우리가 원하는 것은 홀수와 짝수로 구분된 내용을 단일 컬럼을 갖는 10개의 행으로 표현하는 것입니다. 다음 질의를 수행하면, 홀수와 짝수로 구분되어 출력되던 정보가 10개의 행으로 출력되는 것을 확인하실 수 있습니다. 이 질의도 복잡한 질의가 아니므로 풀이과정에 대한 설명은 생략하겠습니다.
Cartesian Product를 사용하여 열을 행으로 변환하기
with test as (
select 'odd' gr, 1 g1, 3 g2, 5 g3, 7 g4, 9 g5 from dual union all
select 'even', 2, 4, 6, 8, 10 from dual
) select decode(cnt, 1, g1, 2, g2, 3, g3, 4, g4, 5, g5) no
from test
, (select level cnt
from dual
connect by level <= 5);
About PIVOT and UNPIVOT
피벗(PIVOT)은 아마도 Microsoft의 엑셀(Excel)과 같은 스프레드 시트(Spread Sheet)에서 처음 들어봤을 것입니다. PIVOT 연산은 특정 열(ROW)에 포함된 값을 그룹핑하여, 그룹에 포함된 값을 열(Column)로 변환하여 추출하며, 언-피벗(UNPIVOT)은 피벗 연산의 반대 개념으로 특정 열의 값을 행으로 변환하여 추출하는 기법입니다.
PIVOT과 UNPIVOT 연산자는 ANSI/ISO SQL:2003에 표준으로 등록되었습니다만, 아직 많은 상용 RDBMS에서 지원하지 않습니다. 심지어 Oracle도 표준으로 등록되고 오랜 시간이 지난 후, Oracle Database 11gR1에서 처음 소개되었습니다.
PIVOT을 사용하여 행을 열로 변경하기
이번 예제에서는 집계 함수와 DECODE 함수를 사용하여 행을 열로 변환하기와 동일한 예제를 활용하여, PIVOT을 활용하여 같은 결과를 얻어보겠습니다.
PIVOT을 사용하여 열을 행으로 변환하기
with test as (
select level no
from dual
connect by level <= 10
)
(select *
from (select no
, decode(mod(no,2), 1, 'odd', 'even') gr
, round(no/2) sup
from test
)
pivot ( max(no) for sup in (1 g1, 2 g2, 3 g3, 4 g4, 5 g5) )
)
order by gr desc;
결과 질의를 보고 어떤 느낌이 드시나요? 이전에 PIVOT 절에 대해 모르셨던 분은 다소 낯선 느낌이 들었을 것 같습니다. 그리고 익숙하지 않은 문법으로 더 어렵게 느껴질 것 같기도 합니다.
이 예제에 대한 풀이를 간단히 진행하겠습니다. PIVOT 절은 다음과 같이 크게 4개의 영역으로 구성됩니다.
PIVOT Syntax
select …
from …
pivot ①[xml]
(②pivot_clause
③pivot_for_clause
④pivot_in_clause)
where …
먼저 ①번 영역부터 살펴보겠습니다. 이 부분은 ④영역의 부분을 동적으로 구성하기 위해 XML Database를 활용할 때 정의합니다. 이 부분에 대한 설명은 다음 링크에서 관련 정보를 얻으실 수 있습니다. 다음 ②번 영역은 각 그룹의 함수를 적용하는 부분으로 그룹으로 관리할 컬럼을 지정합니다. 이 영역에 적용하는 함수로는 MAX, SUM, COUNT 등의 그룹 함수를 활용할 수 있으며, 주어진 요구사항에 알맞은 함수를 선택하면 됩니다. 이 예제에서는 no 값이 중복되지 않으므로 MAX 함수를 선택했습니다. ③번 영역은 PIVOT의 기준이 될 컬럼을 지정합니다. 예제에서는 sup 값에 따라 열을 생성해야 하므로 sup으로 선택했습니다. 마지막으로 ④번 영역은 ③번 영역에서 지정한 컬럼을 구성하기 위한 값을 정의합니다.
PIVOT 절에 대해 더 자세히 알고 싶으신 분은 다음 링크에서 관련 정보를 얻으실 수 있습니다.
UNPIVOT를 사용하여 열을 행으로 변경하기
이번 예제에서는 Cartesian Product를 이용하여 열을 행으로 변환하기와 동일한 예제를 활용하여, UNPIVOT을 활용하여 같은 결과를 얻어보겠습니다.
UNPIVOT을 사용하여 열을 행으로 변환하기
with test as (
select 'odd' gr, 1 g1, 3 g2, 5 g3, 7 g4, 9 g5 from dual union all
select 'even', 2, 4, 6, 8, 10 from dual
) select no
from (select *
from test
)
unpivot ( no for grp in (g1, g2, g3, g4, g5) )
order by 1;
UNPIVOT 절에 대해 더 자세히 알고 싶으신 분은 다음 링크에서 관련 정보를 얻으실 수 있습니다.
Conclusion
지금까지 Oracle에서 지원하는 행과 열을 변환하는 여러 가지 방법에 대해 알아봤습니다. 각 기법의 사용 방법을 정확하게 학습한 후, 행과 열 또는 열과 행을 변환해야 할 때, 이 글에서 배운 내용을 적용하면 복잡한 문제를 쉽게 해결할 수 있습니다.
References
Oracle, Oracle Database 11g, The Top Features for DBAs, and Developers, Oracle [관련링크]
