title: Rolling calendar pivot in sql server?
body: <p>I have managed to build myself a nice little T-SQL statement that pivots scheduling information into monthly columns.</p>
  
  <pre><code>SELECT *
  FROM 
  (
      SELECT 
      t1.customer_code,
      t1.part_number,
      LEFT(DATENAME(month, [formatted_date]),3) as [Month],
      t1.quantity as [quantity] FROM FORECAST_VIEW as t1
  
      where
          quantity &lt;&gt; 0 
          and formatted_date &gt;= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)  
  
  ) as p
  pivot
  (
      sum(quantity)
      for [Month] in (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
  ) as p
  order by customer_code, part_number
  </code></pre>
  
  <p>This produces an output with the columns</p>
  
  <pre><code>customer_code, part_number, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
  </code></pre>
  
  <p>and sums the required part_number quantities in each month.</p>
  
  <p>This works fine and works wonders in a report however if I could get the month columns to start from the current month and go in order for 12 months, then that would be the icing on the cake.</p>
  
  <p>Problem is that I don't know how to do it.</p>
  
  <p>If I ran it today the output would be</p>
  
  <pre><code>customer_code, part_number, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct
  </code></pre>
  
  <p>Does anyone have any tips?</p>
  
  <p>Thanks</p>
  
  <p>EDIT
  Something I just thought of that I won't rule out if I cannot find a solution but its ugly.
  I could put the whole T-SQL statement inside an IF block</p>
  
  <pre><code>IF LEFT(DATENAME(month, GETDATE()),3) = 'Nov'
  BEGIN
  SELECT *
  FROM 
  (
      ...
  ) as p
  pivot
  (   sum(quantity)
      for [Month] in (Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct)
  ) as p
  order by customer_code, part_number
  END
  </code></pre>
  
  <p>and do that 12 times and adjust the pivot months.</p>