DSXchange

Hi,

I have a complex requirment.

I have records like:

id Fe Zn Ag
1 10 20 30
2 5 6 8


Now I want the records like:

id test value
1 Fe 10
1 Zn 20
1 Ag 30
2 Fe 5
2 Zn 6
2 Ag 8


Can we do that in Datastage.....Please help...

Regards,
Arnie.

That requirement is for a "horizontal pivot" and is precisely what the Pivot stage provides.

Use stage variables to store the distinct column headers from the first row, i.e. SvarCol2 is "IF NOT(In.Col2) THEN In.Col2 ELSE SvarCol2". Then derive your output columns from the data and your stage variables. Manually add a line terminator after the column value and write to a sequential file or use the pivot stage to split the rows.

Well I am partly successful....

I am able to get..

Id Value
1 10
1 20
1 30
2 5
2 6
2 8

But was not able to get Fe,Zn,Ag...etc...as column values....


Regards,
Arnie.

If there are less columns (as 3 in your example), you can hardcode them in 3 separate links and funnel them together to get your output.

there are many columns......20 to be exact.....

Regards,
Arnab.

20 is not many.

Could u please explain the funnel logic....I guess Join will be more useful...

here is my requirment

I have records like:

id Fe Zn Ag
1 10 20 30
2 5 6 8


Now I want the records like:

id test value
1 Fe 10
1 Zn 20
1 Ag 30
2 Fe 5
2 Zn 6

My stage variable response also applies when you use the pivotr stage.

Or just include a hard-coded set of fields on the link before the pivot that you've set to the column names and pivot that along with the others.