pivot column names change

pivot을 쓰다보면 컬럼명을 변경하는 경우가 있다

자주는 아니라도 가끔...이럴 때는 하기와 같이 해주면 된다.

 

SELECT
  PVT.Top_DeptNm
  , PVT.GUBUN
  , PVT.Code
  , Target_AddQ = PVT.[Add+Q]
  , Target_1 = PVT.[1]
  , Target_2 = PVT.[2]
  , Target_3 = PVT.[3]
  , Target_4 = PVT.[4]
 FROM (
   SELECT
    A.Top_DeptNm
    , '매출' AS GUBUN
    , A.Code
    , A.QuarterAlias
    , SUM(B.TargetMoney) AS TargetMoney
   FROM @BASIC_QUARTERALIAS A
    LEFT OUTER JOIN (
         SELECT
          A.*, dbo.fnGetTopDepNm__C013(U.DeptId) AS DeptNm
         FROM @TABLE_MONTH A
          LEFT OUTER JOIN Users U ON A.SalesUserId = U.UserId
         WHERE CUST01 = '10'
        ) B ON A.Top_DeptNm = B.DeptNm AND A.Code = B.CUST04 AND A.QuarterAlias = B.QuarterAlias
   GROUP BY A.Top_DeptNm, A.Code, A.QuarterAlias, A.Sort
  ) P
 PIVOT (SUM(TargetMoney) FOR QuarterAlias IN ([Add+Q], [1], [2], [3], [4])) AS PVT