Query to Normalize data for reporting

I am working with a system whose structure I am unable to change.  I want an effecient way to "Denormailze" some of the data for easire reporting.
The attributes needed are ProjectID, BudgetDate, BudgetAmount.
The table has BudgetDate01 through BudgetDate24 and BudgetAmount01 through BudgetAmount24.
I want a view or a temporary table that has 3 attributes ProjectID, BudgetDate, BudgetAmount.  The result set would containt 24 times teh number of records as the original table.  It would be ok to lessen this by including only non zero amounts.
I know I can do a big union query where I type in all of the 01 through 24 fieldnames in 24 individaul sets with a union, is there a more effecient way?  
ehollemanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Patrick MatthewsConnect With a Mentor Commented:
>>I want an effecient way to "Denormailze" some of the data for easire reporting.

Actually, based on your description, the data are already denormalized, and what you want to do is normalize it.

>>I know I can do a big union query...is there a more effecient way?

Not really.  The union approach is absolutely what I would do.
0
 
ehollemanAuthor Commented:
Yes, I want to normalize, not denormalize, typed teh wrong thing.
Does anyone have any more thoughts?I
If it is a big ol' union, can I put in some kind of loop as the fieldnames go from 01 - 24.  
0
 
Patrick MatthewsCommented:
You could do a loop, but that will require dynamic SQL, and will be less efficient than simply running the union query.
0
All Courses

From novice to tech pro — start learning today.