데이터 피벗, 언피벗 (Pivot, Unpivot)

데이터 피벗?

행을 기반으로 하는 데이터를 컬럼을 기반으로 하는 상태로 바꾸는 것.

 

다음같은 내용의 결과가 있다고 할 때,

SELECT empid, custid, SUM(qty) AS SumQty
FROM dbo.Orders
GROUP BY empid, custid;

대상 데이터 확인

 

직원 ID별 (행), 고객별 (컬럼) 의 총 수량을 출력하고자 하는 리포트를 만들고자 한다면. (피벗팅)

피벗팅

모든 피벗팅은 3가지 논리적 프로세싱 단계로 수행되며, 각 단계마다 필요한 요소가 존재한다.

• 그룹핑 - 연관된 그룹핑 또는 행 요소를 필요로 하며,
• 전개 - 연관된 전개 요소 또는 컬럼 요소를 필요로 하며,
• 집계 - 연관된 집계 요소와 집계 함수를 필요로 한다

여기선 각 직원ID마다 결과를 하나의 행으로 출력 (그룹핑)

고객ID별로 수량을 "전개"하며, 그룹핑 요소와 전개 요소를 "결합"해서 결과값을 만들기 위해

데이터를 집계하는 작업이 필요하다.

 

* 표준 SQL을 이용한 피벗팅

SELECT empid
	 , SUM(CASE WHEN custid='A' THEN qty END) AS A
	 , SUM(CASE WHEN custid='B' THEN qty END) AS B
	 , SUM(CASE WHEN custid='C' THEN qty END) AS C
	 , SUM(CASE WHEN custid='D' THEN qty END) AS D
FROM dbo.Orders
GROUP BY empid

표준 SQL 이용한 피벗팅 결과

 

* T-SQL PIVOT 연산자를 이용한 피벗팅

PIVOT이란 테이블 연산자를 제공하며, 기본 형태는 아래와 같다.

SELECT ...

   FROM <원본 테이블이나 테이블표현식>

   PIVOT (<집계함수> (<집계 요소>)

                FOR <전개 요소>

                    IN (<대상 컬럼목록>) ) AS <결과 테이블 별칭>

...;

집계 함수 (SUM) / 집계 요소 (qty) / 전개 요소 (custid) 그리고 대상 컬럼목록인 (A, B, C, D)가 포함된다.

-- T-SQL PIVOT 연산자 활용
SELECT empid
	 , A, B, C, D
FROM (SELECT empid, custid, qty FROM dbo.Orders) AS D
PIVOT (SUM(qty) FOR custid IN (A,B,C,D) ) AS P;

T-SQL Pivot

 

 

데이터 언피벗팅?

데이터를 컬럼을 기반으로 하는 상태에서 행을 기반으로 하는 상태로 바꾸는 기법.

피벗팅된 테이블의 원본 행은 잠재적으로 여러 개의 행으로 바뀌며, 이런 행들은 각각 지정된 원본 컬럼 값을 가지게 된다

 

위의 결과값을 가진 테이블 (EmpCustOrders) 이 있을 때 언피빗텅의 예시

 

* 표준 SQL을 이용한 언피벗팅

1. 복사본을 생성
2. 요소들을 추출한 후
3. 관련없는 연관 항목들을 제거

1. 고객ID를 나타내는 컬럼 A,B,C,D 당 각각의 복사본을 만들어야 한다.

관계형 대수 및 SQL에선 각 행들의 복사본을 만들기 위해선 카티전 곱(크로스 조인)을 사용해야 한다.

SELECT * FROM dbo.EmpCustOrders
CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid)

2. 각 고객의 값을 나타내는 값을 추출한 다음, 값 컬럼 (qty)을 만드는 것. 이를 위해 CASE 식을 사용한다

3. 그리고 외부쿼리에서 NULL값을 제거한다.

SELECT * FROM
(SELECT empid, custid
	  , CASE custid
		WHEN 'A' THEN A
		WHEN 'B' THEN B
		WHEN 'C' THEN C
		WHEN 'D' THEN D
		END AS qty
FROM dbo.EmpCustOrders
CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid) -- CASE 이용
) AS D
WHERE qty IS NOT NULL; -- NULL 제거

 

* T-SQL UNPIVOT 이용한 언피벗팅 예시

SELECT ...

   FROM <원본 테이블이나 테이블표현식>

   UNPIVOT (<원본 컬럼의 값에 대한 대상 컬럼>

            FOR <원본 컬럼의 이름에 대한 대상 컬럼> IN (<원본 컬럼 목록>) ) as <결과 테이블 별칭>

...;

SELECT empid, custid, qty
FROM dbo.EmpCustOrders
UNPIVOT(qty FOR custid IN (A,B,C,D)) AS U