왜 필요한가: 사람이 보기 좋은 표는 행이 아니라 열로 펼쳐져 있다

15편에서 배운 GROUP BY는 여러 행을 하나로 묶어 집계값을 만든다. 예를 들어 부서·직급별 인원수를 구하면 결과는 “부서, 직급, 인원수”라는 3개 컬럼에 여러 행이 나열된 형태다. 그런데 보고서나 엑셀에서 흔히 보는 표는 이런 모양이 아니라, 직급 하나하나가 각각 별도의 열(사원 열, 대리 열, 과장 열…)로 펼쳐진 교차표(cross-tab) 형태다. GROUP BY만으로는 이런 모양을 만들 수 없고, 각 직급마다 CASE WHEN을 반복해서 나열하는 수밖에 없었다. 이 불편을 해결하기 위해 Oracle 11g부터 PIVOT이라는 전용 구문이 추가됐다.

쉽게 말하면: PIVOT은 “세로로 나열된 값들을 가로로 눕혀 열 제목으로 만드는” 기능이고, UNPIVOT은 그 반대로 “가로로 펼쳐진 열들을 다시 세로로 눕히는” 기능이다.

PIVOT: 행을 열로 변환한다

다음과 같은 사원 테이블이 있다고 하자.

“부서별로, 직급마다 연봉 합계가 각각 열로 펼쳐진 표”를 만들고 싶다면 PIVOT을 다음과 같이 쓴다.

SELECT *
FROM 사원
PIVOT (
  SUM(연봉) FOR 직급 IN ('사원' AS 사원, '대리' AS 대리, '과장' AS 과장)
);

PIVOT 구문은 항상 PIVOT (집계함수(값컬럼) FOR 회전기준컬럼 IN (펼칠 값 목록)) 형태를 취한다. 각 요소의 의미를 뜯어보면 다음과 같다.

• 집계함수(값컬럼): 새로 만들어질 각 열을 채울 값을 어떻게 계산할지 정한다. 여기서는 SUM(연봉)이므로 각 열에 연봉 합계가 들어간다.
• FOR 회전기준컬럼: 어떤 컬럼의 값들을 열 제목으로 쓸지 지정한다. 여기서는 직급 컬럼의 값들이 열이 된다.
• IN (펼칠 값 목록): 실제로 열로 만들 값을 명시적으로 나열한다. '사원' AS 사원처럼 AS로 별칭을 주면 그 별칭이 결과 열 이름이 된다.

나머지 컬럼(여기서는 부서)은 PIVOT의 대상도, 집계 대상도 아니므로 자동으로 그룹 기준(행을 구분하는 축)이 된다. 즉 GROUP BY 부서를 명시하지 않아도 PIVOT이 암묵적으로 그 역할을 대신한다. 결과는 다음과 같다.

인사팀에는 과장이 없으므로 과장 열에는 NULL이 채워진다. IN 목록에 명시하지 않은 값(예: 부장)은 결과 열에 아예 나타나지 않는다는 점도 중요하다. PIVOT은 반드시 집계 함수를 요구하므로, 집계 없이 원본 값을 그대로 나열하려는 시도는 오류가 난다.

UNPIVOT: 열을 행으로 되돌린다

반대로 이미 열로 펼쳐진 데이터를 다시 세로 형태로 되돌리는 것이 UNPIVOT이다. 다음과 같은 매출 테이블이 있다고 하자.

SELECT 상품, 월, 금액
FROM 매출
UNPIVOT (
  금액 FOR 월 IN ("1월", "2월", "3월")
);

UNPIVOT 구문은 UNPIVOT (새 값컬럼 FOR 새 항목컬럼 IN (원래 열 목록)) 형태다. 1월, 2월, 3월이라는 세 개의 열을 각각 하나의 행으로 풀어내면서, 열 이름 자체가 월 컬럼의 값이 되고 그 열에 있던 숫자가 금액 컬럼의 값이 된다. 컬럼명에 한글이나 숫자가 섞여 있어 예약어와 혼동될 수 있는 경우 큰따옴표로 감싸 식별자임을 명시한다.

여기서 시험에 자주 나오는 함정이 하나 있다. UNPIVOT은 기본적으로 원래 값이 NULL이었던 항목을 결과에서 제외한다. 위 예시에서 2월 값이 NULL이므로, 별도 옵션 없이 실행하면 결과는 다음과 같이 1월과 3월 행만 나온다.

2월 행 자체가 통째로 빠진다는 점에 주의해야 한다. NULL이었던 항목도 결과에 포함하려면 UNPIVOT INCLUDE NULLS (...)처럼 INCLUDE NULLS 옵션을 명시해야 하며, 기본값은 EXCLUDE NULLS다.

자주 틀리는 점: PIVOT과 UNPIVOT의 방향을 헷갈리는 문제, 그리고 UNPIVOT이 NULL 값을 기본적으로 제외한다는 점을 놓치는 문제가 반복 출제된다. “PIVOT은 행→열, UNPIVOT은 열→행”이라는 방향과, “UNPIVOT은 기본적으로 NULL을 버린다”는 두 가지를 함께 기억해야 한다.

정규표현식 함수 개요

정규표현식(regular expression, 정규식)은 문자열이 특정 패턴과 일치하는지 검사하거나, 패턴에 맞는 부분을 찾아내고 바꾸는 데 쓰는 표기법이다. 예를 들어 “숫자로만 이루어진 문자열인가”, “이메일 형식을 갖췄는가” 같은 조건은 LIKE의 단순한 와일드카드(%, _)만으로는 정확히 표현하기 어렵다. 이럴 때 정규표현식이 훨씬 정교한 패턴 매칭을 제공한다.

Oracle은 다음 네 가지 정규표현식 함수를 제공한다.

패턴 표기에서 자주 쓰이는 기호로는 임의의 한 문자를 뜻하는 ., 0개 이상 반복을 뜻하는 *, 문자열의 시작을 뜻하는 ^, 문자열의 끝을 뜻하는 $, 숫자·문자 등 단어를 구성하는 문자 하나를 뜻하는 \w가 있다. 예를 들어 ^\w*$라는 패턴은 “문자열 전체(시작부터 끝까지)가 단어 구성 문자로만 이루어져야 한다”는 뜻이므로, 공백이나 특수문자가 하나라도 섞여 있으면 이 패턴과 매칭되지 않는다.

SELECT 이름 FROM 사원 WHERE REGEXP_LIKE(전화번호, '^01[0-9]-[0-9]{3,4}-[0-9]{4}$');

이 조건은 “01로 시작하고 그다음 숫자 하나, 하이픈, 3~4자리 숫자, 하이픈, 4자리 숫자”로 이어지는 휴대전화번호 형식과 정확히 일치하는 행만 찾아낸다. SQLD 시험에서는 정규표현식을 처음부터 직접 작성하게 하기보다는, 주어진 패턴이 특정 문자열과 매칭되는지 여부를 판단하는 형태로 주로 출제된다.

기타 활용 구문: DECODE와 CASE WHEN

DECODE와 CASE WHEN은 둘 다 “조건에 따라 다른 값을 반환한다”는 같은 목적을 가진 구문이지만, 표현 방식과 사용 범위가 다르다.

DECODE는 Oracle 전용 함수로, 값이 정확히 일치하는지만 비교할 수 있다.

SELECT 이름, DECODE(직급, '사원', '초급', '대리', '중급', '고급') AS 등급
FROM 사원;

이 식은 “직급이 ‘사원’이면 ‘초급’, ‘대리’면 ‘중급’, 그 외에는 ‘고급’을 반환하라”는 뜻이다. DECODE는 등호(=) 비교만 가능하고, “연봉이 4000 이상이면”과 같은 범위 조건은 표현할 수 없다는 한계가 있다.

CASE WHEN은 표준 SQL 구문으로 범위 조건, 부등호 비교 등 훨씬 유연한 조건을 표현할 수 있다.

SELECT 이름,
  CASE
    WHEN 연봉 >= 5000 THEN '고소득'
    WHEN 연봉 >= 3500 THEN '중간'
    ELSE '초급'
  END AS 등급
FROM 사원;

CASE WHEN은 위에서부터 조건을 순서대로 검사해 처음 참이 되는 조건의 결과를 반환하고, 어떤 조건도 참이 아니면 ELSE의 값을 반환한다(ELSE를 생략하면 NULL). 등호 비교만 필요한 단순한 경우라면 DECODE가 더 간결하지만, 범위 비교나 여러 컬럼을 조합한 복잡한 조건에는 CASE WHEN을 써야 한다.

직접 해보기

부서·직급별 연봉을 PIVOT으로 열로 펼친 다음, 그 반대 방향인 UNPIVOT을 다른 테이블에서 실행해 두 방향을 비교해 봅니다.

SELECT *
FROM (SELECT 부서, 직급, 연봉 FROM 사원 WHERE 부서 IS NOT NULL)
PIVOT (
  SUM(연봉) FOR 직급 IN ('사원' AS 사원, '대리' AS 대리, '과장' AS 과장)
);
 
SELECT 상품, 월, 금액 FROM 매출 UNPIVOT (금액 FOR 월 IN ("1월", "2월", "3월"));
SELECT 상품, 월, 금액 FROM 매출 UNPIVOT INCLUDE NULLS (금액 FOR 월 IN ("1월", "2월", "3월"));

무엇을 보아야 하나: 첫 쿼리에서 부서 몇 개가 열 몇 개짜리 행으로 펼쳐지는지 확인하세요. 두 번째와 세 번째 쿼리는 옵션 하나(INCLUDE NULLS)만 다른데, 결과 행 수가 서로 같은지 다른지, 다르다면 어느 달이 사라지고 나타나는지 비교하세요.

왜 이걸 해보나: UNPIVOT이 기본적으로 NULL 값을 결과에서 조용히 빼버린다는 점은 눈으로 행이 사라지는 것을 직접 봐야 확실히 기억에 남는 함정입니다.

핵심 정리

• PIVOT은 PIVOT (집계함수(값컬럼) FOR 회전기준컬럼 IN (값 목록)) 형태로, 행 데이터를 열로 펼치는 구문이며 집계 함수가 반드시 필요하고 IN에 명시한 값만 열이 된다.
• PIVOT의 대상도 집계 대상도 아닌 나머지 컬럼은 자동으로 그룹 기준이 되어 별도의 GROUP BY가 필요 없다.
• UNPIVOT은 UNPIVOT (값컬럼 FOR 항목컬럼 IN (열 목록)) 형태로 열을 행으로 되돌리며, 기본적으로 원래 NULL이었던 값은 결과에서 제외된다(INCLUDE NULLS로 포함 가능).
• 정규표현식 함수(REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR)는 LIKE보다 정교한 문자열 패턴 매칭을 제공한다.
• DECODE는 등호 비교만 가능한 Oracle 전용 함수이고, CASE WHEN은 범위·부등호 조건까지 표현 가능한 표준 SQL 구문이다.

마무리 복습

참고 자료

• Oracle Database SQL Language Reference - PIVOT Clause 
• Oracle Database SQL Language Reference - Conditions and Regular Expressions