[Oracle] Pivot(피벗) 함수

Oracle의 PIVOT 함수는 행 데이터를 열 데이터로 변환하는데 사용된다. 특히, 요약 데이터를 표현하고나, 데이터 분석 및 보고서를 생성할 때 유용하다. Pivot은 테이블의 데이터를 행렬 형태로 변환하여 보기 쉽게 만들어준다.

Pivot 함수의 주요 구조

SELECT <고정된 열>,
       <피벗된 열들>
FROM (
        SELECT <데이터 원본>
        FROM <테이블 이름>
)
PIVOT (
        <집계함수>(<집계 대상 열>)
        FOR <피벗 열>
        IN (<값1>, <값2>, ... , <값N>)
);

• 고정된 열 : 변환되지 않고 출력에 그대로 유지될 열을 지정한다.
• 데이터 원본 : 피벗 작업을 수행할 테이블 및 데이터를 지정한다.
• 집계 함수 : SUM, AVG, COUNT 등 집계 작업을 수행할 함수를 지정한다.
• 피벗 열 : 행 데이터가 열 데이터로 변환될 기준 열을 지정한다.
• IN : 변환할 특정 값을 지정한다. 열 이름으로 나타난다.

예제1:기본 PIVOT

문제 : 부서별로 각 직책(JOB)별 급여(SALARY)의 합계를 표시하라
테이블 예제 (EMPLOYEES)

Department	JOB	SALARY
HR	Manager	5000
IT	Developer	4000
IT	Tester	3000
HR	Assistant	2500
IT	Developer	3500

SELECT *
FROM (
    SELECT DEPARTMENT, JOB, SALARY
    FROM EMPLOYEES
)
PIVOT (
    SUM(SALARY)
    FOR JOB IN ('Manager' as MANAGER, 'Developer' AS DEVELOPER, 'Tester' AS TESTER, 'Assistant' AS ASSISTANT)
);

결과

DEPARTMENT	MANAGER	DEVELOPER	TESTER	ASSISTANT
HR	5000	NULL	NULL	2500
IT	NULL	7500	3000	NULL

예제2:여러 고정 열 사용

문제 : 부서별로 년도별 매출(SALES)를 요약하라
테이블 (SALES_DATA)

DEPARTMENT	YEAR	SALES
Sales	2021	150000
Sales	2022	170000
HR	2021	50000
HR	2022	60000

select *
from (
    select department, year, sales
    from sales_data
)
pivot (
    sum(sales)
    for year in (2021 as "2021", 2022 as "2022")
);

결과

department	2021	2022
Sales	150000	170000
HR	50000	60000

Pivot 함수의 유용한 팁

1. 동적 Pivot

   • Oracle Pivot은 기본적으로 고정된 값(ex. IN 절의 값)을 사용해야 한다. 만약 동적으로 열을 생성하려면, PL/SQL 또는 동적 SQL을 사용하여 동적으로 쿼리를 생성해야 한다.

2. NULL 값 처리

   • Pivot 결과에서 특정 값이 존재하지 않는 경우, 해당 셀에 NULL 이 표시된다. 필요시 COALESCE를 사용해 기본값으로 대체 가능하다.

     select coalesce(manager, 0) as manager, ...

3. 여러 집계 함수 사용

   • pivot은 한 번에 여러 집계 함수를 결합할 수 없다. 대신 서브쿼리를 중첩하여 다중 집계를 구현해야 한다.

Pivot과 Unpivot의 차이점

• Pivot : 행 데이터를 열로 변환
• Unpivot : 열 데이터를 행으로 변환

Unpivot 예제

select *
from employees
unpivot (
    salary for job in (manager, developer, tester, assistant)
);