Transpose columns into rows from a MySql-Database into a Oracle-DB

[_AnonymousUser]

Hi,
i hope you can help me a little bit. I'm sorry for my bad english.
The Problem is:
I have a Table in an MySQL-Database and want to transpose it to a new Table of an Oracle Database.
Example:
MySql-Table
Id answer1 answer2 answer3 answer5
1 A B C 'xyzui'
2 D E F 'ghf'
3 G H I 'lkjo'
and I want to transform the Table into a New Table of Oracle Database that it looks like this:
id answers answer_value
1 1 A
1 2 B
1 3 C
1 4 'xyzui'
2 1 D
2 2 E
2 3 F
2 4 'ghf'
3 1 G
3 2 H
3 3 I
3 4 'lkjo'

Thanks in advance,
To solve the problem would be very important for me. Thank you once before,
Best regards,
David

[Anonymous]

Hi David
There is a component called tUnpivotRow on Talend exchange can used to convert row to columns. You can learn and download it from the following link:
http://www.talendforge.org/exchange/index.php?eid=148&product=tos&action=view&nav=1,1,1
Here are the steps to install a custom component:
1> Download the component from Talend exchange.
2> Unzip the archive file, copy the component folder and paste it to:
<TOS install dir>/plugins/org.talend.designer.components.localprovider_xxx.xxxxxx/components
3> Restart TOS.
If the installation is successful, the component will appear in the palette.
I show you how to use this component to achieve your request.
in.txt:
Id;answer1;answer2;answer3;answer4
1;A;B;C;'xyzui'
2;D;E;F;'ghf'
3;G;H;I;'lkjo'
result on console:

Starting job forum17323 at 15:49 12/08/2011.
 connecting to socket on port 3737
 connected
.--+---------+-----------.
|       tLogRow_1        |
|=-+---------+----------=|
|id|pivot_key|pivot_value|
|=-+---------+----------=|
|1 |answer1  |A          |
|1 |answer2  |B          |
|1 |answer3  |C          |
|1 |answer4  |'xyzui'    |
|2 |answer1  |D          |
|2 |answer2  |E          |
|2 |answer3  |F          |
|2 |answer4  |'ghf'      |
|3 |answer1  |G          |
|3 |answer2  |H          |
|3 |answer3  |I          |
|3 |answer4  |'lkjo'     |
'--+---------+-----------'
.--+-------+------------.
|       tLogRow_2       |
|=-+-------+-----------=|
|id|answers|answer_value|
|=-+-------+-----------=|
|1 |1      |A           |
|1 |2      |B           |
|1 |3      |C           |
|1 |4      |'xyzui'     |
|2 |1      |D           |
|2 |2      |E           |
|2 |3      |F           |
|2 |4      |'ghf'       |
|3 |1      |G           |
|3 |2      |H           |
|3 |3      |I           |
|3 |4      |'lkjo'      |
'--+-------+------------'
 disconnected
Job forum17323 ended at 15:49 12/08/2011. 

Best regards
Shong

[_AnonymousUser]

Hello,
the prolem is, that i don't know whre i get to this schmas... It isn't the table schema or ?
and can i take the Database-connection instead of the tFileDemlimited ?
here is a screenshot of the actual status..

Tank you in advance,
David

[_AnonymousUser]

Ok, I find it,
but the next problem is, that i can't understand what do you do in your last screenshot... i mean the part with: Numeric.sequence(""+id+",1,1)
I cant know the function numeric.sequence, but i think it adds the value 1 to the colum 'answer_value'. The Proble is that the coumns can go:
answer56, answer57, answer81, answer73, answer58

at the following screenshot you see my status. I thing i am in the near of the end but i have no idea for the last part...

Tank you in advance

[alevy]

Numeric.sequence(name,start,step) will create a variable of the specified name with the specified start value if it doesn't already exists or increment that variable by the step value if it does exist. So shong's example starts at 1 for each unique value in row3.id and increments by 1 for each repetition of that value.
It does not make a difference what order the fields are in the DB. Just write the SQL for the tMysqlInput component with the fields in the right order.
(There is also a standard component in v4.2.0+ called tSplitRow that allow you to do a similar unpivoting of your data. It requires more set up but gives you more control.)

[Anonymous]

Hi David
Alevy's explanation is very good. Only forum member are able to upload a screenshot, please register an account and join us!!!
Note that each image must be less then 1024x768 pixels and 200 KB.
Best regards
Shong

[Anonymous]

Just put "Acquire" against the Name field in the row3 lookup table or filter the row3 lookup data before tMap.
Tera Money
Cheap Tera Gold

[_AnonymousUser]

I want to Thank You,
In this week and in the next week i am develop my final project of my training. The Tables therefore are finished.
Tank you all,
David