transform N columns into N lines

[_AnonymousUser]

Can anybody help me how to go about this.I am a complete beginner to Talend even to any ETL tool..Need to implement batch loading using talend.
Can anybody help me in the below
My input will be a complex join between different tables(oracle databse)
My output is a delimited file(which needs to be ftpd)
My problem is the structure of the output file is as below
id 9429979F6
value 96.5
sectype XYZ
secsubtype XYZ
secsubtyp2 XYZ
Coup 3.32
descript "ABC"
rating1 A
rating1id X97886
id 9229979F6
value 26.5
sectype X2YZ
secsubtype X2Z
secsubtyp2 X2YZ
Coup 3.32
descript "ABC"
rating2 A
rating2id Y9789-78
and so on
The column names are at the left handside(dynamically generated from database eg rating1 exists in database so is present in the first data)
Can anybody help me how to go about this

[Anonymous]

Hi
Use the tOracleInput to get the data from oracle and tFileOutputDelimited component as output file:
tOracleInput--main-->tFileInputDelimited
Please download the user documentation and learn more.
Feel free to post your questions!
Best regards
shong

[_AnonymousUser]

Hi shong
Thanks for the reply..Yeah i went thru the user guide and used the same componenets you mentioned.But how do i specify the format of the file to in the same as the one i mentioned ? .. Please help

[_AnonymousUser]

Hi shong
Thanks for the reply..Yeah i went thru the user guide and used the same componenets you mentioned.But how do i specify the format of the file to be the same as the one i mentioned ? The left hand side names are the columns names of the resultant query and should be generated automatically.. Please help

Hi
Use the tOracleInput to get the data from oracle and tFileOutputDelimited component as output file:
tOracleInput--main-->tFileInputDelimited
Please download the user documentation and learn more.
Feel free to post your questions!
Best regards
shong

[Anonymous]

Hi

The left hand side names are the columns names of the resultant query and should be generated automatically.

After defining the achema, click the 'Guess Query' button to generated the query statement automatically.(see screenshots)
Best regards
shong

[_AnonymousUser]

Hi Shong
Thanks for your help ..As i said the headings are dynamically generated.
For eg uppose my id can have any one or more of colums rating1,rating2,rating3,rating3.S
id 9429979F6 has a rating1 so we should get
id 9429979F6
value 96.5
sectype XYZ
secsubtype XYZ
secsubtyp2 XYZ
Coup 3.32
descript "ABC"
rating1 A
rating1id X97886
id 9229979F6 has rating2 so the this field is included in the details
id 9229979F6
value 26.5
sectype X2YZ
secsubtype X2Z
secsubtyp2 X2YZ
Coup 3.32
descript "ABC"
rating2 A
rating2id Y9789-78
So here some columns in the output file depend on whether there is value in the database for that column..Hope this explains my problemIs there any way we could implement this

[_AnonymousUser]

Can anybody please help me??

[c0utta]

Hi abrar,
The issue that you are having is not related to TOS, but is related to the query that you'll need to run against the Oracle table. TOS will only work with a known structure, so an unknown number of columns (rating1, rating2 .. ratingN) is not possible.
I believe it will require a specialised SQL Query in the first instance - then creating the output file is easy.
For the output you've provided, can you post the source Oracle row(s)? What are the column names and the primary key? This is minimal information that is required to help you.
Cheers,
c0utta

[_AnonymousUser]

Hi c0utta
Thanks for the reply ... i will change my sql accordingly..Can i get the output text file in the format i mentioned.The Column heading at the left side and followed by the corresponding values..If yes how do i do it ?
id 9429979F6
value 96.5
sectype XYZ
secsubtype XYZ
secsubtyp2 XYZ
Coup 3.32
descript "ABC"
rating1 A
rating1id X97886
id 9229979F6
value 26.5
sectype X2YZ
secsubtype X2Z
secsubtyp2 X2YZ
Coup 3.32
descript "ABC"
rating2 A
rating2id Y9789-78
id 922329F6
value 206.5
sectype X22YZ
secsubtype AX2Z
secsubtyp2 AX2YZ
Coup 3.32
descript "ABC"
rating2 A
rating2id Y9789-78
and so on.....
Thanks

[amaumont]

You could use a tMap to concatenate columns of each row such as this in an only one String typed column in the output table :
- Connect your DBInput to the main input of a tMap.
- Add a variable "separator" with the value as you want (" " for example)
- Add an output and an output column in the tMap :

 
"id" + separator + row1.id + "\n"
"value" + separator + row1.value + "\n"
"sectype" + separator + row1.sectype + "\n"
"secsubtype" + separator + row1.secsubtype + "\n"
"secsubtyp2" + separator + row1.secsubtyp2 + "\n"
"Coup" + separator + row1.Coup + "\n"
"descript" + separator + row1.descript + "\n"
"rating1" + separator + row1.rating1 + "\n"
"rating1id" + separator + row1.rating1id + "\n"
+ "\n"

- Add your file output delimited (with only one column).
Maybe \n will be escaped in tFileOutputDelimited, so you could copy it and modify it to remove this effect if necessary.
It should work, but I don't have time today to test it in a real case.
I will try tomorrow this solution.