PIVOT XML

PIVOT을 이용한 년도별 입사 인원수 구하기

SELECT * 
FROM (SELECT to_char(hire_date, 'yyyy') year, count(*) cnt
        FROM hr.employees
        GROUP BY to_char(hire_date, 'yyyy'))
PIVOT (max(cnt) FOR year in ('2001' "2001", '2002' "2002", '2003' "2003", '2004' "2004", '2005' "2005", '2006' "2006", '2007' "2007", '2008' "2008"));

오늘 PIVOT에 대해 배우며 한 가지 의문점이 생겼다.

피봇 값들을 꼭 이렇게 상수로 하나하나 적어줘야 하는걸까?

이 쿼리에서는 입사 연도가 2001년부터 2008년까지 8개밖에 없었지만, 더 많은 연도가 있었으면 그 많은 연도를 하나하나 작성하는 것도 귀찮고, 중간에 빠지거나 잘못 작성하는 것도 생길지 모르는데 너무 비효율적이라고 생각했다.

서브 쿼리 사용하기

그래서 이 문제를 해결하기 위해 내가 생각한 방법은 in 절에 서브쿼리를 사용하는 것이었다.

SELECT *
FROM (SELECT to_char(hire_date, 'yyyy') year, count(*) cnt
        FROM hr.employees
        GROUP BY to_char(hire_date, 'yyyy'))
PIVOT (max(cnt) FOR year in (select to_char(hire_date, 'yyyy') from hr.employees));

띠용... 오류가 발생했다.

그래서 오라클 공식 문서를 찾아봤다.

SQL Language Reference

 

IN ( { { { expr
         | ( expr [, expr]... )
         } [ [ AS] alias]
       }...
     | subquery
     | ANY [, ANY]...
     }
   )

서브쿼리 가능...?! 그럼 왜 오류나는거지??

PIVOT XML

아하... 서브쿼리는 XML 키워드를 함께 쓸 때만 가능하단다.

PIVOT은 기본적으로 IN 절에 상수 값만 받을 수 있는데, 서브 쿼리와 같이 피벗 대상 값들을 미리 알 수 없는 경우에는 XML 키워드를 통해 피벗 컬럼 값이 쿼리 실행 시점에 평가되도록 할 수 있다고 한다.

자, 그럼 직접 사용해보자

SELECT *
FROM (SELECT to_char(hire_date, 'yyyy') year, count(*) cnt
        FROM hr.employees
        GROUP BY to_char(hire_date, 'yyyy'))
PIVOT XML (max(cnt) FOR year in (select to_char(hire_date, 'yyyy') from hr.employees));

이건 또 뭐야...

우리의 친구 챗지피티씨에게 물어보니 PIVOT XML은 각 피벗 값을 <item> 단위로 묶어 XML 형태로 반환한다고 한다. 그래서 결과가 표 형태가 아닌 XML 형태로 표현되기 때문에 XMLSERIALIZE를 사용하면 XML 안의 값을 확인할 수 있다고 한다.

SELECT XMLSERIALIZE(CONTENT year_xml as clob indent size = 2) as xml_text
FROM (
		SELECT *
		FROM (SELECT to_char(hire_date, 'yyyy') year, count(*) cnt
        		FROM hr.employees
        		GROUP BY to_char(hire_date, 'yyyy'))
		PIVOT xml (max(cnt) FOR year in (select to_char(hire_date, 'yyyy') from hr.employees)
      )
);

짠~ 결과 확인 완료!

결론

PIVOT XML을 사용하면 동적으로 피봇 값을 설정할 수는 있지만, 결과가 XML 형태로 나오기 때문에 아래 테이블 형식처럼 보기는 어렵다.

따라서 위와 같은 결과를 얻고 싶다면, 슬프게도 피봇 값을 직접 작성해주어야 한다. ㅠㅠ

PL/SQL을 사용해서 쿼리를 동적으로 만들어서 실행하면 가능할 것 같은데, 아직 PL/SQL은 안 배웠으니깐 다음에 배우고나서 한 번 해보는걸로~~

---

2025/11/13 추가

PL/SQL을 활용한 PIVOT 컬럼 동적 생성

-- 바인드 커서 선언
VAR rc REFCURSOR;

DECLARE
    v_sql   VARCHAR2(4000);
    v_cols  VARCHAR2(4000);
BEGIN
    -- Pivot 컬럼 목록 동적으로 추출
    SELECT LISTAGG( '''' || year || ''' AS "' || year || '"' , ',')
           WITHIN GROUP (ORDER BY year)
    INTO v_cols
    FROM (
        SELECT DISTINCT to_char(hire_date, 'YYYY') year
        FROM hr.employees
    );

    -- Dynamic Pivot SQL 조립
    v_sql := 'SELECT *
FROM (
    SELECT to_char(hire_date, ''YYYY'') AS year, COUNT(*) AS cnt
    FROM hr.employees
    GROUP BY to_char(hire_date, ''YYYY'')
)
PIVOT (
    MAX(cnt) FOR year IN (' || v_cols || ')
)
    ';

    -- 확인용 출력
    DBMS_OUTPUT.PUT_LINE(v_sql);
    
    -- v_sql 실행 결과셋 바인드 커서에 연결
    OPEN :rc FOR v_sql;
END;
/

-- 결과 출력
print rc;