Need to bring things to the technical side from a data perspective for a question.
I have a process that I'm working on rebuilding in SSIS, migrating from a Foxpro program. The process presents values per group per type in a normalized fashion. In the Foxpro version the code built what might be considered a pivot table and then used code to normalize it out. I've built the solution to start in a normalized form but now I've hit a section that has me stumped on the best way to translate it. Simply put, I have a table with something akin to the following: Group | Code | Amount Group 1 | Code 1 | 150.00 Group 1 | Code 2 | 50.00 Group 1 | Code 3 | 25.00 Group 2 | Code 3 | 185.00 Group 2 | Code 4 | 50.00 Group 2 | Code 5 | 10.00 In a "true" pivot form this looks like this: Group | Code 1 | Code 2 | Code 3 | Code 4 | Code 5 Group 1 | 150.00 | 50.00 | 25.00 | 0.00 | 0.00 Group 2 | 0.00 | 0.00 | 185.00 | 50.00 | 10.00 I need to actually have it look more like this: Group | Code 1 | Code 2 | Code 3 | Code 4 | Code 5 Group 1 | 150.00 | 50.00 | 210.00 | 50.00 | 10.00 Group 2 | 150.00 | 50.00 | 210.00 | 50.00 | 10.00 Effectively I have to take the sum of the values per code and apply it to both groups, this is because I then have a multiplier to apply to all of the values. These multipliers add up to 1. My question is this - which tool or tools in SSIS would I use to accomplish this or is that even the right place for this (would it be better to run in a SP). This is only one aspect of the process that I'm rebuilding. Any thoughts? Thanks! Until Later! C. Hatton Humphrey http://www.eastcoastconservative.com Every cloud does have a silver lining. Sometimes you just have to do some smelting to find it. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-community/message.cfm/messageid:366441 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
