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