how to transpose a table?

One Star

how to transpose a table?

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.
One Star

Re: how to transpose a table?

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

Re: how to transpose a table?

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

Re: how to transpose a table?

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
One Star

Re: how to transpose a table?

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

Re: how to transpose a table?

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

Re: how to transpose a table?

Or you can check the ecosystem and find the dedicated component