PIVOT 처리

크로스탭? 가로 세로를 변경 하는 쿼리는 년이나 월의 내역을 조회 표현할때 가끔 사용합니다.
MSSQL 2005이상부터는 PIVOT이 지원하기 떄문에 좀 더 간편하게 처리가 가능합니다.

실제 프로젝트에서 적용해서 처리할려고보니 호환성이 2000이군요. 2000을 마이그레이션한 표준DB라서 그런지 호환성 올리기도 싫고 귀찮고 해서 그냥 테스트만 해봅니다.


우선 2000버전에서 구현시 쿼리 예제

SELECT SUM(AMT1) AMT1,SUM(AMT2) AMT2,SUM(AMT3) AMT3,SUM(AMT4) AMT4,SUM(AMT5) AMT5
 ,SUM(AMT6) AMT6,SUM(AMT7) AMT7,SUM(AMT8) AMT8,SUM(AMT9) AMT9,SUM(AMT10) AMT10,SUM(AMT11) AMT11,SUM(AMT12) AMT12
 , SUM(AMT1) +SUM(AMT2) +SUM(AMT3) +SUM(AMT4) +SUM(AMT5)
 +SUM(AMT6) +SUM(AMT7) +SUM(AMT8) +SUM(AMT9) +SUM(AMT10) +SUM(AMT11) +SUM(AMT12) AS SUM_AMT
 , ACCT_CD, ORG_CHANGE_ID, DEPT_CD, ACCT_NM
 FROM (
SELECT    
a.DEPT_CD, a.ACCT_CD, a.ORG_CHANGE_ID, d.ACCT_NM
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '01' THEN A.BDG_AMT ELSE 0 END AS AMT1
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '02' THEN A.BDG_AMT ELSE 0 END AS AMT2
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '03' THEN A.BDG_AMT ELSE 0 END AS AMT3
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '04' THEN A.BDG_AMT ELSE 0 END AS AMT4
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '05' THEN A.BDG_AMT ELSE 0 END AS AMT5
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '06' THEN A.BDG_AMT ELSE 0 END AS AMT6
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '07' THEN A.BDG_AMT ELSE 0 END AS AMT7
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '08' THEN A.BDG_AMT ELSE 0 END AS AMT8
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '09' THEN A.BDG_AMT ELSE 0 END AS AMT9
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '10' THEN A.BDG_AMT ELSE 0 END AS AMT10
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '11' THEN A.BDG_AMT ELSE 0 END AS AMT11
,CASE SUBSTRING(BDG_YYYYMM,6,2) WHEN '12' THEN A.BDG_AMT ELSE 0 END AS AMT12

FROM         F_BDG_DEPT_MASTER_KO532 AS a
 INNER JOIN
                       F_BDG_DPT_KO532 AS b ON a.DEPT_CD = b.DEPT_CD AND a.ACCT_CD = b.ACCT_CD
                       LEFT OUTER JOIN F_BDG_DEPT_KO532 AS c ON a.DEPT_CD = c.DEPT_CD
                       LEFT OUTER JOIN F_BDG_ACCT_KO532 AS d ON a.ACCT_CD = d.ACCT_CD
WHERE
BDG_YYYYMM BETWEEN @Fr_Dt AND @To_Dt
AND a.DEPT_CD = @DEPT_CD
) Z
GROUP BY ACCT_CD, ORG_CHANGE_ID, DEPT_CD, ACCT_NM

대충 보면 케이스로 월별 분류 처리해서 GROUP으로 가로를 세로를 나열합니다.

위 같은 결과가 나옵니다. 이걸 PIVOT으로 구현해보면

SELECT *
   FROM
  ( 
SELECT    
a.DEPT_CD, a.ACCT_CD, a.ORG_CHANGE_ID, d.ACCT_NM, SUBSTRING(BDG_YYYYMM,6,2) as MM, a.BDG_AMT
FROM         F_BDG_DEPT_MASTER_KO532 AS a
 INNER JOIN
                       F_BDG_DPT_KO532 AS b ON a.DEPT_CD = b.DEPT_CD AND a.ACCT_CD = b.ACCT_CD
                       LEFT OUTER JOIN F_BDG_DEPT_KO532 AS c ON a.DEPT_CD = c.DEPT_CD
                       LEFT OUTER JOIN F_BDG_ACCT_KO532 AS d ON a.ACCT_CD = d.ACCT_CD
                      
WHERE
BDG_YYYYMM BETWEEN @Fr_Dt AND @To_Dt
AND a.DEPT_CD = @DEPT_CD
) z
PIVOT ( SUM(BDG_AMT) FOR MM IN ([01], [02], [03], [04] ,[05], [06], [07] ,[08] ,[09] ,[10], [11], [12])) as AMT
--UNPIVOT( BDG_AMT FOR [UNPIVOT] IN ( [01], [02], [03],[04], [05], [06], [07],[08], [09], [10], [11], [12] )) AS UNAMT

위처럼 구현이 가능합니다. 주석인 UNPIVOT으로 다시 세로로 변환도 가능하지만 이건 가능한것만 보여준것이고 실제 UNPIVOT만 구현해보면 아래 처럼

SELECT *, @BDG_YYYY + '-' + UNAMT.[UNPIVOT] as BDG_YYYYMM
   FROM
  (SELECT @DEPT_CD dept_cd, @ACCT_CD acct_cd, @AMT1 [01], @AMT2 [02], @AMT3 [03], @AMT4 [04],@AMT5 [05], @AMT6 [06], @AMT7 [07],@AMT8 [08]
  ,@AMT9 [09],@AMT10 [10], @AMT11 [11], @AMT12 [12]) AS AMT
  UNPIVOT( BDG_AMT FOR [UNPIVOT] IN ( [01], [02], [03],[04], [05], [06], [07],[08], [09], [10], [11], [12] )) AS UNAMT

되겠죠.. 그럼 원래의 세로 내역이 표시됩니다 :)

이걸 프로그램단에서 LINQ로 구현해보려고 하니 아직은 개념이 안잡히네요. 공부 좀 더 해야겠습니다..