DECLARE @cols AS NVARCHAR(MAX) DECLARE @query AS NVARCHAR(MAX); SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME( CONVERT(VARCHAR(4),YEAR(she.OrderDate))) AS Year FROM Production.Location loc INNER JOIN Production.ProductInventory pin ON loc.LocationID = pin.LocationID INNER JOIN Sales.SalesOrderDetail sde ON pin.ProductID = sde.ProductID INNER JOIN sales.SalesOrderHeader she ON sde.SalesOrderID = she.SalesOrderID ORDER BY YEAR ASC FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') print @cols set @query = 'SELECT name AS LocationName, ' + @cols + ' from ( SELECT loc.name , sde.OrderQty , YEAR(she.OrderDate) AS Year FROM Production.Location loc INNER JOIN Production.ProductInventory pin ON loc.LocationID = pin.LocationID INNER JOIN Sales.SalesOrderDetail sde ON pin.ProductID = sde.ProductID INNER JOIN sales.SalesOrderHeader she ON sde.SalesOrderID = she.SalesOrderID ) x pivot ( SUM(OrderQty) FOR Year IN (' + @cols + ') ) p ' EXECUTE(@query)