Execute the following SQL Server T-SQL script in SSMS Query Editor to demonstrate the use of dynamic SQL and PIVOT operator to create a summary report.

USE AdventureWorks2008;

DECLARE @SQL NVARCHAR(max), @PivotYears VARCHAR(MAX)

DECLARE @DDYear TABLE ([Year] int)

-- Get years for pivoting dynamically

INSERT @DDYear ([Year])

 SELECT * FROM (SELECT DISTINCT DDYear=YEAR(DueDate)

 FROM Purchasing.PurchaseOrderDetail ) x

 ORDER BY DDYear

-- Form list for PIVOT statement

SELECT @PivotYears = COALESCE(@PivotYears +

                     ',[', '[') + CONVERT(CHAR(4),[Year]) + ']'

FROM @DDYear

-- Assemble query

SET @SQL = 'SELECT ProductName,

[2001]=COALESCE([2001], 0),

[2002]=COALESCE([2002], 0),

[2003]=COALESCE([2003], 0),

[2004]=COALESCE([2004], 0)

 FROM (SELECT  ProductName=p.Name,  YEAR(DueDate) OrderYear,

 OrderQty

  FROM Purchasing.PurchaseOrderDetail pod

  JOIN Production.Product p

  ON pod.ProductID = p.ProductID) x

 PIVOT (SUM(OrderQty) FOR OrderYear IN (' + @PivotYears + ')) pvt

 ORDER BY ProductName'

PRINT @SQL  -- debug print to Messages window

EXEC sp_executeSQL @SQL

GO

Partial results: