title: How to create multiple rows from a single row - normalize a table
body: <p>I am pretty new to SQL and trying to figure this out:</p>
  
  <p>I have a table called BUDGET that has 12 columns for each month of the year, displaying the budget balance of that month. So the table looks like this:</p>
  
  <pre><code>[Department]  [Year]  [Month1] [Month2] .... [Month12]  
  ABCD           2010   $5000     $5500   .....  $4000
  ABCD           2011   $6000     $6500   .....  $3000
  </code></pre>
  
  <p>What I am trying to do is to normalize this table and break each row into 12 rows, each row with a date field in the following format. I also want to have a [Balance] column that displays the value of that month. So, the normalized table will look like this:</p>
  
  <pre><code>[Department]  [Date]     [Balance] 
  ABCD          20100101     $5000   
  ABCD          20100201     $5500 
  ABCD          20100301     .....
  ABCD          .......      ......
  </code></pre>
  
  <p>I tried using CROSS JOIN on the same table but failed. I also tried using a while loop but that failed as well. Any kind of help is appreciated. Thanks!</p>
  
  <p>EDIT: I am using SQL Server 2008</p>