[DATABASE] PIVOT / UNPIVOT

PIVOT

• 행을 열로 바꿔주는 연산자이다.
• 주로 값 집계 + 가로 형태의 데이터 요약에 사용된다.
• GROUP BY와 유사한 집계 목적이지만 결과를 세로 방향이 아닌 가로로 회전시켜 보여준다.
• 주로 리포트, 대시보드, 요약 통계에서 자주 사용된다.

PIVOT 기본 문법

SELECT 
    PIVOT FOR에 있는 행에서 열로 바꿀 컬럼, PIVOT IN안에 있는 컬럼, 값 컬럼	-- * 도 가능
FROM (
    SELECT 행으로 되어 있는 값들
    FROM 테이블명
) AS 테이블별칭
PIVOT (
    SUM(집계할 값)
    FOR 행에서 열로 바꿀 컬럼 IN ([열1], [열2], ...)
) AS 피봇테이블 별칭;

• 테이블 별칭과 피봇 테이블 별칭은 마음대로 부여해도 된다.
• 테이블 별칭은 별도로 PIVOT 안에서 쓰인지 않는다

PIVOT 예시

• 학생의 과목별 성적을 보고 싶다.
• LEFT JOIN과 PIVOT 둘의 차이를 비교

PIVOT 사용안한 LEFT JOIN 쿼리문 조회 결과

• 이렇게 보면 한눈에 잘 들어 오지 않는다.
• 학생이름이 여러번 나오기 때문에 한 학생이 어떤 과목을 어떤 점수를 받았는지 과목(SUBJECT)을 기준으로 보고 싶다는 요청이 왔다고 가정  

PIVOT 사용 결과

• PIVOT 사용시 한 학생이 과목별로 어떤 점수를 받았는지 한눈에 확인하기 쉽다.

쿼리문

SELECT 
    *							
FROM (
   SELECT 
      ST.STUDENT_NAME,
      SC.SUBJECT,
      SC.SCORE
   FROM STUDENT ST LEFT JOIN SCORE SC 
	ON ST.STUDENT_SEQ = SC.STUDENT_SEQ
) AS T							-- 별칭을 부여하긴 하나 따로 사용처는 없음
PIVOT (
   SUM(SCORE)               				-- 집계할 대상
   FOR SUBJECT IN ([수학], [국어], [과학], [체육])	-- 행을 열로 바꿀 기준
) AS SCORE_BOARD

PIVOT 사용시 주의사항

• PIVOT 사용시 FOR 안에 '기준' 이 동적으로 변경 될 수 있음
• 예시로 위 쿼리문에 '수학', '국어', '과학', '체육' 만 있는데 과목이 늘어나면 쿼리문도 하드코딩해서 수동으로 변경해줘야 하는 번거로움이 있음
• 관리자가 직접 손수 관리하고 있다면 상관 없지만 SCORE 테이블의 과목(SUBJECT)가 사용자 등록에 따라 추가 생성되거나 변경 되는 경우 바로 대응하기가 어려움
• 그래서 동적쿼리를 활용해 PIVOT을 사용 
• 왠만하면 동적쿼리를 사용해 PIVOT을 사용하는게 좋음

동적쿼리로 변환한 PIVOT

-- 변수 선언
DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SET @cols = ''	-- cols변수에 초기 값 할당

-- 과목(SUBJECT) 추출해 [] 대괄호로 감싼걸 cols 변수에 할당
SELECT @cols = @cols + '[' + SUBJECT + '],'
FROM (
		SELECT 
			SUBJECT
		FROM SCORE
		GROUP BY SUBJECT
	) AS SUBJECTS

-- 마지막 쉼표 제거
SET @cols = LEFT(@cols, LEN(@cols) - 1);

-- 쿼리문 만들기
SET @sql = '
	SELECT *
	FROM (
		SELECT 
			ST.STUDENT_NAME,
			SC.SUBJECT,
			SC.SCORE
		FROM STUDENT ST LEFT JOIN SCORE SC
			ON ST.STUDENT_SEQ = SC.STUDENT_SEQ
	) AS T
	PIVOT (
		SUM(SCORE)
		FOR SUBJECT IN (' + @cols + ')
	) AS SCORE_BOARD
	';

-- 실행 (sp_executesql => 내장 저장 프로시저)
EXEC sp_executesql @sql;

• 이렇게 동적쿼리로 하면 기준이 되는 과목(SUBJECT)가 동적으로 늘어나거나 줄어들어도 따로 쿼리 수정안해도 된다.

---

UNPIVOT

• 열을 행으로 바꿔주는 연산자이다.
• 비정규화 되어 있는 테이블을 정규화 테이블 처럼 만들고 싶을 때
• PIVOT된 테이블을 다시 원래 구조로 복구하고 싶을 때 등등 사용

UNPIVOT 기본 문법

SELECT 
	고정 컬럼, 속성 컬럼, 값 컬럼	-- * 도 가능
FROM (
    SELECT 고정 컬럼, 속성1, 속성2, ... 
    FROM 테이블명
) AS 원본
UNPIVOT (
    값 컬럼 FOR 속성 컬럼 IN ([속성1], [속성2], ...)
) AS 별칭;

UNPIVOT 예시

• 학생(STUDENT) 테이블에 희망직업1, 2, 3 (JOB1, 2, 3) 이 있다.
• 비정규화 되어 있는 상태이다.
• 비정규화가 되어 있어 학생들의 직업을 찾을려면 JOB1 ~ 3 까지 다찾아야 하는 번거로움이 있어 이걸 정규화하려고 한다.

결과

쿼리문

SELECT 
	*
FROM (
    SELECT 
        STUDENT_NAME,
        STUDENT_AGE,
        JOB1,
        JOB2,
        JOB3
    FROM STUDENT
) AS INFO
UNPIVOT (
	HOPE_JOB FOR JOB IN ([JOB1], [JOB2], [JOB3])
) STUDENT_JOB

이해를 돕기 위한 설명

1. 쿼리문 설명

쿼리문 설명

2. UNPIVOT 사용시 열이 행으로 변경될 때 설명

UNPIVOT이 열을 행으로 바꿀 때의 설명

---