I am using a corporate ETL of MYABCM to transform some Excel spreadsheets to MYSQL, the problem is that I do not know how to transform the table on the left in the table on the right. The logic is that the element in column F of the table on the LEFT is the element "father" which has 6 relations, column A to F (itself). Then the procedure (which can not be a store procedure) must be able to take each element of the above column F and replicate it in column I of the table to the RIGHT, one row per relation, from O1 to O6 and so on. If we see the example E.01.01.01.01 in the table on the right is repeated for each relation, first row would be something like. Column H = OC (always fixed) column I is the own one (E.01.01.01.01.01) column J corresponds to the location (O1 to 06 fixed and always the same), the "E" value of the K column always goes to be O1 for each element "FATHER" the "E.01" will always be O2 and so on for each "father".
OC E.01.01.01.01.01 O1 E
OC E.01.01.01.01.01 02 E.01
I was thinking something like taking the value of column F and inserting it 6 times (it's always the same), with each insert insert OC (fixed), the value itself, 01 for the first insertion and maybe substr (value, 1.1). For the second one
OC VALUE O2 substr (value, 1,4)
For the third
OC Value O3 substr (value, 1,7)
etc.
OC Value O4 substr (value, 1.10)
OC Value O5 substr (value, 1,13)
OC Value O6 substr (value, 1,16)
The problem is like iterating over the "F" value