피벗테이블

피벗테이블이란?

테이블을 조회한 데이터를 특정 데이터 컬럼으로 사용, 요약된 결과를 만들어 표시하는 것입니다.

사용자 입장에서 데이터를 좀 더 쉽게 볼 수있도록 출력 형태를 가공할때 사용합니다.

 

예제

피벗테이블을 활용하여 세로로 되어있는 칼럼을 가로로 바꿔보는 예제 (행을 열로 변환)

MM_TEST 테이블안에는 위와같이 DT(날짜),QT(수량)의 데이터가 10만개가 있습니다.

위 테이블의 월별 합계 수량을 가로로 나타내시오

 

해결방법

1. 테이블의 월별 합계 수량을 Select 합니다.

DECLARE @DT_FROM NVARCHAR(6) = '200802'

DECLARE @DT_TO NVARCHAR(6) = '200904'

SELECT MAX(DT) AS DT, SUM(QT) AS QT FROM MM_TEST

WHERE DT BETWEEN @DT_FROM AND @DT_TO

GROUP BY DT

ORDER BY DT

 

2. Select한 쿼리문을 피벗테이블을 활용하여 데이터를 가공합니다. (행,열 전환)

DECLARE @DT_FROM NVARCHAR(6) = '200802'

DECLARE @DT_TO NVARCHAR(6) = '200904'

SELECT * FROM

( SELECT MAX(DT) AS DT, SUM(QT) AS QT FROM MM_TEST

WHERE DT BETWEEN @DT_FROM AND @DT_TO

GROUP BY DT )Q

PIVOT (

SUM(QT) FOR DT IN ([200802],[200803],[200804],[200805],[200806],[200807],[200808],[200809],[200810],[200811],[200812],[200901],[200902],[200903],[200904])

)P

 

 

3. 정적 피벗테이블을 동적 피벗테이블로 전환합니다.

DECLARE @DT_FROM NVARCHAR(6) = '200802'; --첫번째칼럼

DECLARE @DT_TO NVARCHAR(6) = '200904'; --마지막컬럼

DECLARE @DT_NO NVARCHAR(6); --칼럼(하나)

DECLARE @DT_LAST NVARCHAR(300); --합쳐진 칼럼(문자열)

SET @DT_LAST = '' --초기화

DECLARE MYCUR CURSOR FOR --커서 선언

SELECT A.DT FROM (

SELECT DT FROM MM_TEST

WHERE DT BETWEEN @DT_FROM AND @DT_TO

GROUP BY DT)A

ORDER BY DT

OPEN MYCUR

FETCH NEXT FROM MYCUR INTO @DT_NO

WHILE(@@FETCH_STATUS=0) --반복문

BEGIN

SET @DT_LAST = @DT_LAST + '['+@DT_NO+'],' --칼럼 합치기

FETCH NEXT FROM MYCUR INTO @DT_NO

END

CLOSE MYCUR

DEALLOCATE MYCUR --반복문 종료

SET @DT_LAST = LEFT(@DT_LAST, LEN(@DT_LAST)-1) --마지막 ,제거

EXEC('

SELECT * FROM( SELECT DT, SUM(QT) AS QT FROM MM_TEST

WHERE DT BETWEEN '+@DT_FROM+' AND '+@DT_TO+ '

GROUP BY DT )Q

PIVOT ( SUM(QT) FOR DT IN ('+ @DT_LAST +') )

AS P') --문자열 쿼리 실행

GO

 

피벗테이블안에는 동적으로 파라미터를 받을 수 없습니다.

그러므로 피벗테이블 쿼리를 문자열로 만들어 그 문자열을 EXEC시켜주는 방식으로 동적 피벗테이블을 구현합니다.

 

최종 결과