Hi,
I've searched the forum for vertical pivoting, there is ton of information but my problem is little different, I have to transform row into columns and then populate the values based on the column names.
for example.
The Input is :
Key_col1 col_name col_value type
---------------------------------------------
1 A 10 alpha
1 B 20 alpha
1 C 30 alpha
2 A 40 beta
2 B 50 beta
2 C 60 beta
3 A 70 teta
3 B 80 teta
----------------------------------------------
Output should look like this
Key_col1 A B C Type
----------------------------------------------
1 10 20 30 aplha
2 40 50 60 beta
3 70 80 0 teta
-----------------------------------------------
I have used hash file lookup as shown below to obtain this. it works but has very poor perfomance. as we have to transform about 100 columns and around 600K rows.
Src------->XFM--------->Hsh1
^
|
|
|
Hsh1
And then thought of using stage variables like suggested in the forum but having tough time trying to populate the values in the correct column.
I tried to concatenate the value and the col_name and append them in CSV format. like this
1,1,A10,B20,C30.
but then couldn't figure out how to separate them to populate in the respective fields.
Right now we are processing everything using a sql (temporarily) which is not bad in performance but would really like to use DataStage for doing this.
Thank you very much.
Vertical pivoting based on the value of column names
Moderators: chulett, rschirm, roy
Vertical pivoting based on the value of column names
Thanks,
Kiran
Kiran
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse