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

Question

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.

Answer 1

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.