topic Re: how to transpose a table? in Talend Studio

how to transpose a table?

Anonymous — Sat, 16 Nov 2024 14:13:49 GMT

hi,
this is my input table:
h1, val1a, val2a, valNa
h2, val1b, val2b, valNb
h3, val1c, val2c, valNc

i want this output table:
h1, h2, h3
val1a, val1b, val1c
val2a, val2b, val2c
valNa, valNb, valNc

is there any simple or complicated way to do this?
my input is an excel file, and my output is a sql server table. I can put the excel in a delimited text file if it's necessary.
many thanks.

Re: how to transpose a table?

Anonymous — Thu, 11 Sep 2008 21:33:09 GMT

Hi,
which generation language do you use? Is your data dynamic (if so in which directions)?
Bye
Volker

Re: how to transpose a table?

Anonymous — Thu, 11 Sep 2008 21:58:21 GMT

i use java, and my data is not dynamic, thanks in advance

Re: how to transpose a table?

Anonymous — Thu, 11 Sep 2008 22:06:04 GMT

Not so easy...
The problem is that you should first read all rows and then you can create the output. So you have to read your data multiple times.
One idea:
Create a fix flow with only one attribute: a sequence number starting with null. Than you iterate over the same file and read for every row the nth value. To extract this you can use split in java for example.
One night later another (i find better) idea:
a) Read your data row by row with all columns in one attribute (for example use tFileInputRegex with "^(.*)$")
b) In a tMap add a sequence to your data
c) use tDeNormalize to break down your data.
d) Use another tMap to add a second sequence. Set the sequence key to the first sequence created in b)
You should now have now the following:
1, 1, h1
1, 2, val1a
1, 3, val2a
1, 4, valNa
2, 1, h2
2, 2, val1b
e) Now go the same way back with changed sequence keys: sort your data on the second sequence, remove the first and use tNormalize to bring all together.
Now you have:
1, "h1, h2, h3"
2, "val1a, val1b, val1c"
We are now back on step a) but with transposed data. You can now save this and read it again or use java code to get the values (split for example).
Bye
Volker

Re: how to transpose a table?

Anonymous — Fri, 12 Sep 2008 16:13:30 GMT

thank you very much, it works.. but
c) i have to use a tNormalize
e) i have to use a tDeNormalize
bye
Daniele.

Re: how to transpose a table?

Anonymous — Fri, 12 Sep 2008 21:33:44 GMT

Yes you are right. I mixed them...
Bye
Volker

Re: how to transpose a table?

Anonymous — Mon, 15 Sep 2008 09:56:30 GMT

Or you can check the ecosystem and find the dedicated component