How to create multiple rows from a single row - normalize a table

Question

I am pretty new to SQL and trying to figure this out:

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:

[Department]  [Year]  [Month1] [Month2] .... [Month12]  
ABCD           2010   $5000     $5500   .....  $4000
ABCD           2011   $6000     $6500   .....  $3000

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:

[Department]  [Date]     [Balance] 
ABCD          20100101     $5000   
ABCD          20100201     $5500 
ABCD          20100301     .....
ABCD          .......      ......

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!

EDIT: I am using SQL Server 2008

Answer 1

Just for fun here's a CROSS APPLY solution:

SELECT
   B.Department,
   DateAdd(month, (B.Year - 1900) * 12 + M.Mo - 1, 0) [Date],
   M.Balance
FROM
   dbo.Budget B
   CROSS APPLY (
      VALUES
      (1, Month1), (2, Month2), (3, Month3), (4, Month4), (5, Month5), (6, Month6),
      (7, Month7), (8, Month8), (9, Month9), (10, Month10), (11, Month11), (12, Month12)
   ) M (Mo, Balance);

It's really no different than @Aaron Bertrand's UNPIVOT, without using UNPIVOT.

If you must have the date as a string, then put strings in the CROSS APPLY like ('01', Month1) and change the SELECT to Convert(char(4), B.Year) + M.Mo.