eholleman
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could do a loop, but that will require dynamic SQL, and will be less efficient than simply running the union query.
ASKER
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.