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