title: Dynamic SQL query to return results in columns
body: <p>I have this basic database table and am SQL beginner. </p>
  
  <pre><code>+-------------+--------+----------+
  |  location   |  Week  | Expenses |
  +-------------+--------+----------+
  | Backoffice  | 201851 |       80 |
  | frontoffice | 201852 |      110 |
  | Backoffice  | 201901 |      120 |
  | Backoffice  | 201902 |       70 |
  | frontoffice | 201903 |       68 |
  +-------------+--------+----------+
  </code></pre>
  
  <p>Is there a way to dynamically retrieve the last 5 weeks result, instead of hard coding it every time in my pivot table?</p>
  
  <p>Here is my code:</p>
  
  <pre><code>  SELECT *  from
    (SELECT[week]
          ,[Expenses]
  from [cafe].[dbo].[table]where location = 'Backoffice'
  ) as Total_Expenses pivot (sum([expenses]) for [week] in ([201902],[201903],[201904],[201905],[201906],[201907],[201908],[201909])) as pivotable;
  </code></pre>
  
  <p>I would like to be able to just enter "5" and it shows me the last 5 week. May be by a "max" formula" </p>