Figure 13 Constructing a Dynamic Query String
　

CREATE INDEX idx_nc_OrderDate ON SalesOrderHeader(OrderDate)

DECLARE @sql AS NVARCHAR(4000), @dt AS DATETIME

SET @sql = 
  N'WITH OrdersCTE(SalesPersonID, OrderYear, OrderValue)
AS
(
  SELECT
    SOH.SalesPersonID, 
    YEAR(SOH.OrderDate),
    SOD.OrderQty * SOD.UnitPrice
  FROM SalesOrderHeader AS SOH
    JOIN SalesOrderDetail AS SOD
      ON SOD.SalesOrderID = SOH.SalesOrderID
)
SELECT *
FROM OrdersCTE
  PIVOT(SUM(OrderValue) FOR OrderYear IN('

SET @dt = 
  (SELECT CONVERT(CHAR(4), YEAR(MIN(OrderDate))) + '0101'
   FROM SalesOrderHeader)

WHILE @dt IS NOT NULL
BEGIN
  SET @sql = @sql + N'[' + CAST(YEAR(@dt) AS NCHAR(4)) + N']'

  SET @dt = 
    (SELECT CAST(YEAR(MIN(OrderDate)) AS CHAR(4)) + '0101'
     FROM SalesOrderHeader
     WHERE OrderDate >= DATEADD(year, 1, @dt))

  IF @dt IS NOT NULL SET @sql = @sql + N','
END

SET @sql = @sql + N')) AS P'

EXEC sp_executesql @sql