Columns to rows

[sriec12]

Need logic for the following data

ST CD AMT
100 X 10
100 Y 20
100 Z 30
101 X 10
101 Y 20
101 Z 30
102 X 10
102 Y 20
102 Z 30


Output will be

ST X Y Z
100 30 60 90
101 30 60 90
102 30 60 90

Currently I am using 3 Aggregate states but is there any other way to implement this ?


ISSUE: I have 10 codes to Aggregate I am using 10 Aggregate stages to get the out put

[crystal_pup]

You can use Pivot stage for your requirement. As per the example that you have provided, I am not able to understand how you are getting X=30,Y=60 and Z=90 for ST=100. Kindly explain.

[sriec12]

IF CD =X I am sending values to Aggregation stage so I will get 30... in the same way rest

[sriec12]

It can achieved through vertical pivoting in Pivot enterprise stage............I dont see this option in datastage 8.1 ?

[mobashshar]

If you are using DS 8.5 or above, consider using Transformer Loop function. For reference check http://newtonapples.com/datastage-trans ... looping-1/

Or if you are using DS 8.1 try using key break logic viewtopic.php?p=423754

[prasson_ibm]

Hi,

Take two output link from source. In one output link do aggregation on amt based on CD. Your output will be below:-
X,30
Y,60
Z,90

In another output,you need to pivot CD based on ST,output should be:-
100 X Y Z
101 X Y Z
102 X Y z

Now do left outer join,making link2 on left,and in output of join stage pass sum amount,it is your expexted output.