Q:

Is it possible to use dynamically created values in a SQL PIVOT?

I have the following function: -
ALTER FUNCTION fncTest ()
RETURNS TABLE
AS
  RETURN
    (SELECT *
     FROM   (SELECT TOP 100 PERCENT sOrderType,
                                    SUM(iQty) AS iOrdQty,
                                    ( YEAR(dReqd) * 100 ) + DATEPART(Week, dReqd) AS iWkNo
             FROM   tblOrderBook
             GROUP  BY sOrderType,
                       dOrdered) AS tblTemp PIVOT(SUM(Qty) FOR iWkNo IN 
                        ([201118], [201119], [201120], [201121], [201122])) AS pvtTemp)  

This gives me a pivoted table showing qtys of orders for weeks 18-22 of 2011.
Is it possible to replace the hard-coded weeks with dynamic dates based on GETDATE().
ie:

replace [201118] with (YEAR(GETDATE()+7)*100)+DATEPART(week,GETDATE()+7)
replace [200119] with (YEAR(GETDATE()+14)*100)+DATEPART(week,GETDATE()+14)
replace [200120] with (YEAR(GETDATE()+21)*100)+DATEPART(week,GETDATE()+21)

etc...
Thanks.

A:

Not as you want to do it. The only way of doing this is dynamic SQL and you can't use dynamic SQL in a function.
But you could use fixed column names such as [1],[2],[3] etc to represent the week number relative to the current date.

