피벗테이블이란?
테이블을 조회한 데이터를 특정 데이터 컬럼으로 사용, 요약된 결과를 만들어 표시하는 것입니다.
사용자 입장에서 데이터를 좀 더 쉽게 볼 수있도록 출력 형태를 가공할때 사용합니다.
예제
피벗테이블을 활용하여 세로로 되어있는 칼럼을 가로로 바꿔보는 예제 (행을 열로 변환)
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시켜주는 방식으로 동적 피벗테이블을 구현합니다.
최종 결과
'IT이야기 > MS-SQL' 카테고리의 다른 글
| 조건문 (CASE WHEN, IF) 함수 사용법 & 예제 (0) | 2019.10.29 |
|---|---|
| SET NOCOUNT 사용법 (0) | 2019.10.29 |
| 프로시저 사용법 (0) | 2019.10.29 |
| 저장 프로시져 내용 검색 (0) | 2019.10.24 |
| MSSQL 함수 모음 (0) | 2019.10.24 |