Vertical pivoting based on the value of column names

[vanama]

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.

[samsuf2002]

search for vertical pivot in the forum u will find bunch of topics related to it

[gateleys]

Try this:
viewtopic.php?t=89442&highlight=vertical+pivot