[MSSQL] 동적 피벗 테이블 만들기

이번 포스팅에서는 앞선 두 포스팅에서 배웠던

문자열 합치기 : STUFF, FOR XML PATH

문자열 구분자 추가 : QUOTENAME

기법들을 활용해 동적 피벗테이블을 만들어보도록 하겠습니다.

 

 동적 피벗 테이블 만들기 예제 

테이블 DEPT, EMP를 이용해서 연도별(DT) 부서 입사 현황을 조회한다

(회사코드(COMPANY):0327, 사업장코드(BIZAREA):1000 데이터로 한정함)

 

1. PIVOT을 이용해 년도를 칼럼으로 표시

2. 동적컬럼 생성 시 STUFF, QUOTENAME 함수를 이용하여 입사자가 있는 년도만 표시

 

결과 화면 예시

 

동적 피벗 만들기

DECLARE @COL AS NVARCHAR(MAX) --변수선언
DECLARE @QUERY AS NVARCHAR(MAX)

--변수에 쿼리결과 저장--
SELECT @COL = STUFF((SELECT ',' + QUOTENAME(SUBSTRING(DT,0,5))
FROM EMP
WHERE
BIZAREA = '1000' AND
COMPANY = '0327' AND
DT IS NOT NULL   
GROUP BY SUBSTRING(DT,0,5)
ORDER BY SUBSTRING(DT,0,5)
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)') 
,1,1,'')

 --동적피벗만들기--
SET @QUERY =
'SELECT * FROM
(
SELECT
A.CD_DEPT,
A.NM_DEPT AS 부서명,
SUBSTRING(B.DT,0,5) AS DT
FROM DEPT A
LEFT OUTER JOIN EMP B ON A.COMPANY = B.COMPANY AND 
A.DEPT = B.DEPT AND A.BIZAREA = B.BIZAREA
WHERE
A.BIZAREA = ''1000'' AND
A.COMPANY = ''0327'' AND
B.DT IS NOT NULL
)Q
PIVOT
(
COUNT(DEPT) FOR DT IN ('+ @COL +')
)P'
EXECUTE(@QUERY);