title: Repeating Record Sequence using SQL
body: <p>This could easily be done using code, but I wondered if it could be done at the database level using SQL Server (2008). </p>
  
  <p>I have a table similar to below:</p>
  
  <pre><code>CROP_ID   YEAR_   PRODUCTION   
  1         1       0  
  1         2       300  
  1         3       500  
  2         1       100
  2         2       700
  </code></pre>
  
  <p>I want to be able to run a query to repeat this for <em>n</em> number of years, per crop type e.g.  </p>
  
  <pre><code>CROP_ID  YEAR_  PRODUCTION  
  1        1      0  
  1        2      300  
  1        3      500  
  1        4      0  
  1        5      300
  1        6      500
  etc.
  </code></pre>
  
  <p>I'm not sure of the best approach, I presume I would need a SP and pass in a year variable, and use a loop construct? However the exact syntax escapes me. Any help appreciated. </p>
  
  <p><b>Update</b></p>
  
  <p>Sorry for not providing all the information in my original post. The table will allow for multiple crop types, and for Produciton values to be updated so Case statements with fixed variables are not really suitable. Apologies for not being clearer. </p>
  
  <p><b>Update</b></p>
  
  <p>With the TVF answer I used the following modified SQL to select by CropType for 20 years. </p>
  
  <pre><code>select top 20 b.CROP_ID,  
  YEAR_ = n.num * (select count() from MyBaseTable where CROP_ID = 3) + b.YEAR,  
  b.PRODUCTION from MyBaseTable b, dbo.fnMakeNRows(20) n  
  where CROP_ID = 3  
  </code></pre>