Normalize Mysql Table from horizontal to vertical

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
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!!

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now