동적 피벗 테이블

피벗(PIVOT)의 사전적 의미는 마찰을 적게 하기 위하여 회전하는 축의 지점 끝을 원뿔꼴로 한 것이다. 이 의미를 데이터베이스에서 나름 해석해보자면 테이블을 90도 회전 시켜 열을 행으로 바꿔버리는 그런 느낌의 의미라 할 수 있을 것이다. 

DECLARE @COL AS NVARCHAR(MAX)
DECLARE @QUERY AS NVARCHAR(MAX)


SELECT * FROM SALE 

SELECT DISTINCT
	@COL = STUFF((
		SELECT DISTINCT ',' + QUOTENAME(YYYYMM)
		FROM SALE
		FOR	XML PATH('')
	  ),1,1,'') 
FROM SALE 
SELECT @COL


SET @QUERY = 'SELECT * FROM 
(
SELECT  * FROM SALE
) AS RESULT 
PIVOT( SUM(AMT) FOR YYYYMM IN ('+@COL+'))AS pivot_result'

EXECUTE(@QUERY)

 

우선 아래와 같이 SALE 테이블을 만들어보자

 

SALE TABLE

다 만들었으면 아래와 같이 코드를 만들고 실행하여 보자

SELECT DISTINCT
	@COL = STUFF((
		SELECT DISTINCT ',' + QUOTENAME(YYYYMM)
		FROM SALE
		FOR	XML PATH('')
	  ),1,1,'') 
FROM SALE 
SELECT @COL

STUFF, XML PATH 를 사용하여 YYYMM의 데이터를 한열로 요약하였다.

위 이미지와 같은 결과가 나타나게된다. 위의 결과를 자세히 살펴보면 YYYYMM 속성의 값들이 중복 없이 일열로 나열된 것을 확인할 수 있다. 이를 위해서 STUFF((쿼리문 FOR XML PATH('') ),1,1,'')을 활용하면 된다.  

STUFF에 관해 구체적으로 궁금하다면 아래 사이트를 참조하길 바란다. 

[MSSQL] STUFF와 FOR XML PATH를 활용한 문자열 합치기 (tistory.com)

[MSSQL] STUFF와 FOR XML PATH를 활용한 문자열 합치기

그리고 아직 설명하지 않은 QUOTENAME()이 남아있다. QUOTENAME()이 어떤 기능을 가졌는지 이해하기 위해서 우선 QUOTENAME을 없애고 위의 코드를 실행시키면 아래와 같이 [] 대괄호가 사라진 것을 확인할 수 있다. 이 사실로 미루어 보아 QUOTENAME이 대괄호를 만들어주는 역할을 수행하는 것을 알 수 있지만 왜 필요한지 의문이 들 수 있다. 어느 블로그에서 확인한 바로는 피벗 테이블에서 속성으로 만들어주기 위해서는 MSSQL에서는 대괄호로 감싸져 있어야 한다고 한다.

 

이제 아래와 같이 피벗 테이블을 만들어주는 쿼리문을 작성하여 보자. 

SET @QUERY = 'SELECT * FROM 
(
SELECT  * FROM SALE
) AS RESULT 
PIVOT( SUM(AMT) FOR YYYYMM IN ('+@COL+'))AS pivot_result'

EXECUTE(@QUERY)

 피벗 테이블의 일반적인 구조는 아래와 같다. 

SELECT *
FROM (SELECT * FROM 피벗을 원하는 테이블) AS RESULT
PIVOT ( 그룹함수(집계컬럼) FOR 피벗대상컬럼 IN ([피벗컬럼값], ...) AS PIVOT_RESULT

 

 

일반 테이블을 피벗 테이블로 완성시킨 모습

 

PIVOT에 대해 추가적으로 학습하고 싶다면 아래의 블로그를 활용하길 바란다. 

[MSSQL] PIVOT 함수, 행을 열로 바꾸기 (크로스탭, 동적컬럼) (tistory.com)

[MSSQL] PIVOT 함수, 행을 열로 바꾸기 (크로스탭, 동적컬럼)