One Star

Normalize Mysql Table from horizontal to vertical

Hi,
i need to normalize an horizontal Mysql table to a vertical one. For example, from the format:
id | Col1 | Col2 | Col3 | Col4 | Col5
id1| data11| data12| data13| data14| data15
id2| data21| data22| data23 | data24| data25
to the format:
id | Col1
id | Col2
id | Col3
id | Col4
id | Col5
id1 | data11
id1 | data 12
... ...
I searched a lot here but this is done in talend via csv, but this is not the case.
I didn't find any component that does this kind of normalization, i'm trying to use a tJavaRow but it seems limited.
What would you use to make it?
Thanks
5 REPLIES
Four Stars

Re: Normalize Mysql Table from horizontal to vertical

Hi,
Please check similar thread
http://www.talendforge.org/forum/viewtopic.php?id=36207
Where I have given few steps to achieve the objective which is similar to your requirement
Vaibhav
Five Stars

Re: Normalize Mysql Table from horizontal to vertical

yes you can normalise your data using tNormalize component. i am able to obtain desire output as you mention in initial post.
in tmap i have concatenated all the columns in single one except id column.

row7.Column1+";"+row7.Column2+";"+row7.Column3+";"+row7.Column4+";"+row7.Column5

check the pictures for more information.
One Star

Re: Normalize Mysql Table from horizontal to vertical

actually this doesn't fit good with my scenario.
i prefer to take a Mysql Input with the row table shown before and transform each row into an array,
then pass the array to the Mysql Output.
This seems possible with a tJavaFlex, but i actually don't know if it's possibile with talend.
Do you have any hint?
Five Stars

Re: Normalize Mysql Table from horizontal to vertical

instead file you can connect with MySQL and follow the same way, and pass output to MySQL. make me correct if i am wrong.
One Star

Re: Normalize Mysql Table from horizontal to vertical

yes, it's right, i'm quite there.
I didn't mentioned that i need to associate another column to the table, to normalize it:
id | Col1 | id_of_Col1
id | Col2 | id_of_Col2
id | Col3 | id_of_Col3
id | Col4 | id_of_Col4
id | Col5 | id_of_Col5
id1 | data11 | id_of_Col1
id1 | data 12 | id_of_Col2
in order to associate every column to the related row.
What could the next step be?
Thank you for your help!!