I hope you can help find an answer to a problem that will become a recurring theme at work. This involves denormalising data from RDBMS tables to flat file formats with repeating groups (sharing domain and meaning) across columns. Unfortunately this is unavoidable.
Here's a very simplified example of the transformation I'd require:
TABLE A TABLE B ------------------- 1 -> MANY ---------------------------- A_KEY FIELD_A B_KEY A_KEY FIELD_B A_KEY_01 A_VALUE_01 B_KEY_01 A_KEY_01 B_VALUE_01 A_KEY_02 A_VALUE_02 B_KEY_02 A_KEY_01 B_VALUE_02 B_KEY_03 A_KEY_02 B_VALUE_03
This will become:
A_KEY FIELD_A B_KEY1 FIELD_B1 B_KEY2 FIELD_B2 A_KEY_01 A_VALUE_01 B_KEY_01 B_VALUE_01 B_KEY_02 B_VALUE_02 A_KEY_02 A_VALUE_02 B_KEY_03 B_VALUE_03
Each entry from TABLE A
will have one row in the output flat file with one column per related field from TABLE B
. Columns in the output file can have empty values for fields obtained from TABLE B
.
I realise this will create an extremely wide file, but this is a requirement. I've had a look at MapForce and Apatar, but I think this problem is too bizarre or I can't use them correctly.
My question: is there already a tool that will accomplish this or should I develop one from scratch (I don't want to reinvent the wheel)?
Thanks for all your help. As it turns out the relationship is ONE -> MAX of 3 and this constraint will not change as the data is now static so the following run-of-the-mill SQL works:
select A.A_KEY, A.FIELD_A, B.B_KEY, B.FIELD_B, B2.B_KEY, B2.FIELD_B, B3.B_KEY, B3.FIELD_B from A left join B on (A.A_KEY = B.A_KEY) left join B B2 on (A.A_KEY = B2.A_KEY and B2.B_KEY != B.B_KEY) left join B B3 on (A.A_KEY = B3.A_KEY and B3.B_KEY != B.B_KEY and B3.B_KEY != B2.B_KEY) group by A.A_KEY order by A.A_KEY