NG 202X

크로스탭? 가로 세로를 변경 하는 쿼리는 년이나 월의 내역을 조회 표현할때 가끔 사용합니다.
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로 구현해보려고 하니 아직은 개념이 안잡히네요. 공부 좀 더 해야겠습니다..

듕국의 DB를 한쿡에서 마이그레이션을 하라고해서 해보니 정렬방식에서 오류가 빵 터지네요.

입력되는, 즉 원본 테이블의 MailDivision 열의 정렬 방식은 Chinese_PRC_CI_AS (=936) 으로 되어 있는데,
저장되는 대상 테이블의 MailDivision 열의 정렬 방식은 Korean_Wansung_CI_AS (=949) 로 되어 있기 때문에
이 차이로 인해 발생하는 문제

라고 http://www.sqlleader.com 에서 찾았답니다.

해서 해결책이 2가지 나와있는데 1번 방안으로 처리했더니 큰 문제없이 처리되었습니다.
2번은 어딘지 모르겠네요. 난 바보..
물론 손이 고생했습니다. OTL

1. 원본 테이블에서 MailDivision을 읽어올 때 열의 정렬 방식을 바꾸는 방법

   SELECT ... , MailDivision COLLATE Korean_Wansung_CI_AS AS MailDivision

   이와 같이 문자열의 정렬 방식을 바꿔서, 즉 대상 테이블의 것과 같도록 읽어오는 방법

2. 데이터 원본을 선택한 후, 속성 창에서 AlwaysUseDefaultCodePage 속성을 True로 설정한 후,
   데이터 원본을 다시 한 번 열었다가 닫습니다.

   이 옵션의 역할은 읽어오는 테이블의 데이터 정렬 유형이 어찌되었든 상관 없이 패키지의 Default Code Page로 변환해서 읽어오라는 방식입니다.


참고로, 이러한 정렬 방식은 문자형 열에만 영향

난 머리가 나쁘니까 그림으로

2005때에는 막 수정되었는데 말이죠 ''a