[SQL]PIVOT使用，將公司階層轉向給 report 頁尾使用+UNPIVOT

select 'SIGSEQ',[050],[100],[110],[120],[130],[140],[150],[160],[170],[180],[190],[200] from
(
SELECT
A.FLOWDPTY003
--,A.FLOWDPTY007
--,B.JOBTITLEID
,C.JOBTITLENM
  FROM [dbo].[FLOWDPTY] AS A
  left join [IFLOW2].[dbo].[VW_HPMUSER] AS B ON A.FLOWDPTY007 = B.USERID AND A.FLOWDPTY008 = B.COMPID
  left join [IFLOW2].[dbo].[VW_HPMJOBTITLE] AS C ON B.JOBTITLEID = C.JOBTITLEID AND B.COMPID =C.COMPID
  where A.FLOWDPTY001='IFFIB05F'
 AND A.FLOWDPTY002 = @I_CHR_SHEETNO
 AND A.FLOWDPTY003 BETWEEN '050' AND '200'
) as t
PIVOT(
Max(JOBTITLENM)
for [FLOWDPTY003] in ([050],[100],[110],[120],[130],[140],[150],[160],[170],[180],[190],[200])
) as p;

轉向後

原始

UNPIVOT

SELECT * FROM (

SELECT

A.CCID

,'工作檔' AS VISION

,@SQL_COMPID AS COMPID

      ,A.DATAYEAR

  ,A.SEG1

  ,A.SEG2

  ,C.SEG2NM

  ,A.SEG3

  ,C.SEG3NM

  ,A.SEG4

      ,C.SEG4NM AS SEG4NM

      ,A.SEG5

  ,C.SEG5NM AS SEG5NM

      ,A.SEG6

      ,C.SEG6NM AS SEG6NM

  , A.AMT_M1

      , A.AMT_M2

      , A.AMT_M3

      , A.AMT_M4

      , A.AMT_M5

      , A.AMT_M6

      , A.AMT_M7

      , A.AMT_M8

      , A.AMT_M9

      , A.AMT_M10

      , A.AMT_M11

      , A.AMT_M12

FROM dbo.BJMF AS A

LEFT JOIN dbo.V_ALLACCID_FROM_GL AS C ON A.CCID = C.CCID

WHERE  

A.DATAYEAR = @I_CHR_DATAYEAR

AND A.YEARTYPE = @I_CHR_YEARTYPE

and A.SEG1 = @SQL_COMPID

and A.SEG2 = CASE WHEN @I_CHR_SEG2 <> '' THEN  @I_CHR_SEG2 ELSE A.SEG2 END

and A.SEG3 = CASE WHEN @I_CHR_SEG3 <> '' THEN  @I_CHR_SEG3 ELSE A.SEG3 END

and A.SEG4 = CASE WHEN @I_CHR_SEG4 <> '' THEN  @I_CHR_SEG4 ELSE A.SEG4 END

and A.SEG5 = CASE WHEN @I_CHR_SEG5 <> '' THEN  @I_CHR_SEG5 ELSE A.SEG5 END

and A.SEG6 = CASE WHEN @I_CHR_SEG6 <> '' THEN  @I_CHR_SEG6 ELSE A.SEG6 END

) AS P

UNPIVOT

        (

          MONTH_AMT FOR DATAMONTH IN (AMT_M1,AMT_M2,AMT_M3,AMT_M4,AMT_M5,AMT_M6,AMT_M7,AMT_M8,AMT_M9,AMT_M10,AMT_M11,AMT_M12)

        ) AS pv