Execute the following script in Query Editor to demonstrate the design of a dyncamic PIVOT:

USE AdventureWorks
GO

DECLARE @OrderYear AS TABLE(YYYY int NOT NULL PRIMARY KEY)
DECLARE @DynamicSQL AS nvarchar(4000)
INSERT INTO @OrderYear
SELECT DISTINCT YEAR(OrderDate)
FROM Sales.SalesOrderHeader

DECLARE @ReportColumnNames AS nvarchar(MAX), @IterationYear AS int
SET @IterationYear = (SELECT MIN(YYYY) FROM @OrderYear)
SET @ReportColumnNames = N''
WHILE (@IterationYear is not null)
BEGIN
SET @ReportColumnNames = @ReportColumnNames + N',['+CAST(@IterationYear AS nvarchar(10))+N']'
SET @IterationYear = (SELECT MIN(YYYY) FROM @OrderYear WHERE YYYY > @IterationYear)
END

SET @ReportColumnNames = SUBSTRING(@ReportColumnNames, 2, LEN(@ReportColumnNames))

SET @DynamicSQL = N'SELECT *
FROM (SELECT [Store (Freight Summary)]=s.Name, YEAR(OrderDate) AS OrderYear,
Freight = convert(money,convert(varchar, Freight))
FROM Sales.SalesOrderHeader soh
JOIN Sales.Store s
ON soh.CustomerID = s.CustomerID) as Header
PIVOT (SUM(Freight) FOR OrderYear IN(' + @ReportColumnNames + N')) AS Pvt
ORDER BY 1'
PRINT @DynamicSQL
EXEC sp_executesql @DynamicSQL
GO