Pivot - Static and Dynamic

--Static

SELECT * into zzCER

FROM #CER

PIVOT(SUM(ExchRate)

      FOR Period IN ([2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019])) AS PVTTable

         select Code, z.* from zzCer z join CurrencyMaster CM on z.CurID=cm.ID Order by Code

--Dynamic

If OBJECT_ID('zzCER') is not null

       drop table zzCER

Code	CurName	Period	ExchRate
CAD	CAD	2010	1.05
USD	USD	2010	1
CAD	CAD	2011	1
USD	USD	2011	1
CAD	CAD	2012	1.02
USD	USD	2012	1
CAD	CAD	2013	1
USD	USD	2013	1
CAD	CAD	2014	1.06
USD	USD	2014	1

select Code,cm.CurName, Year(ExchPeriod)Period, ExchRate into zzCER from CurrencyExchRate CER join CurrencyMaster CM on CER.CurID = CM.ID  where month(exchperiod)=1

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

DECLARE @ColumnName AS NVARCHAR(MAX)

If OBJECT_ID('tempdb..#CC') is not null

       drop table #CC

SELECT DISTINCT Period into #CC FROM zzCER Where Period between 2012 and 2019 Order by convert(int,Period)

SELECT @ColumnName= ISNULL(@ColumnName + ',','')

       + QUOTENAME(Period) From #CC Order by  convert(int,Period)

--select @ColumnName

--Prepare the PIVOT query using the dynamic

SET @DynamicPivotQuery =

  N'SELECT Code,CurName, ' + @ColumnName + '

    FROM zzCER

    PIVOT(SUM(ExchRate)

          FOR Period IN (' + @ColumnName + ')) AS PVTTable Order by Code'

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery