Help to perfom Vertical Pivot

[snassimr]

Hi !

I need to perform folowing task

Table 1 :

id Col1 Col2 Col 3
1 2 NULL NULL
1 NULL 3 NULL
1 NULL NULL 4

I need to group by according ID and to create one row in target table :

id Col1 Col2 Col 3
1 2 3 4

I there any STAGE to do it fast ? It seems the task is something opposite of task pefomed by stage PIVOT

[ray.wurlod]

There's no stage for performing a vertical pivot (which is what you are asking for). Search the forum for "vertical pivot"; you should get at least two techniques, each involving an intermediate passive stage.

[kumar_s]

Hi snassimr,
I guess it is quiet stringt forward, use a transformer to make all NULL to 0, and a aggregator to group by "id" and find the max of the col1 col2 col3.


regards
kumar

[elavenil]

As Kumar suggessted, you can achieve this using Aggregator stage. You can use Max or Sum function to get everything in single row.

Regards
Saravanan

[snassimr]

In your solution your assume that the Cols type is integer .
It seem from my example. And if it strings if there any way to do it ?

[kumar_s]

HI,
Not necessarly, even integer can be made to show up NULL.
simple way to convert to integer, if it is string and then proceed.

regards
kumar

[Sainath.Srinivasan]

Did you try using stage variables?