Page 1 of 1
Columns to rows
Posted: Thu Jun 27, 2013 7:50 am
by 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
Posted: Thu Jun 27, 2013 8:37 am
by 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.
Posted: Thu Jun 27, 2013 8:46 am
by sriec12
IF CD =X I am sending values to Aggregation stage so I will get 30... in the same way rest
Posted: Thu Jun 27, 2013 9:18 am
by sriec12
It can achieved through vertical pivoting in Pivot enterprise stage............I dont see this option in datastage 8.1 ?
Posted: Thu Jun 27, 2013 1:35 pm
by 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
Posted: Thu Jun 27, 2013 3:35 pm
by 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.