動態欄位

DECLARE @start CHAR(6), @stop CHAR(6)

DECLARE @workgroup NVARCHAR(max)

DECLARE @pivot NVARCHAR(max)

SELECT @start = '201512'

SELECT @stop = '201612'

SELECT @workgroup = coalesce(@workgroup + ',', '') + quotename('w' +

CASE

WHEN io_month = @start THEN 'b12'

ELSE CAST(RIGHT(io_month, 2) AS CHAR(2))

END )

FROM EMP_R_0022

WHERE io_month BETWEEN @start AND @stop

AND RIGHT(io_month, 2) != '13'

GROUP BY quotename(io_month), io_month

ORDER BY io_month

--SELECT @workgroup

SELECT @pivot = '

SELECT ww.oo, ww.dept_name, ' + @workgroup + ' FROM

(SELECT oo, dept_name, ' + @workgroup + '

FROM

(SELECT oo, dept_name, ''w'' +

CASE

WHEN io_month = ''' + @start + ''' then ''b12''

else CAST(RIGHT(io_month, 2) AS CHAR(2))

END as iomonth, work FROM EMP_R_0022

WHERE io_month BETWEEN ''' + @start + ''' AND ''' + @stop + ''') AS dd

pivot (

sum(work)

FOR iomonth IN ( ' + @workgroup + ' )

)

AS ww) AS ww'

--SELECT @pivot

EXEC sp_executesql @pivot