eno ename
1 qaz
1 wsx
1 edc
2 zxc
2 asd
3 qwe
3 wer
3 tru
4 rgj
Output:
eno ename count
1 qaz,wsx,edc 3
2 zxc,asd 2
3 qwe,wer,tru 3
4 rgj 1
I want the above output to be solved by DataStage as well and I have to write SQL query for the same output.
Answers were Sorted based on User's Feedback
Answer / himanshu jain
you can achieve this with AGGREGATOR and PIVOT ENTERPRISE stage in DATASTAGE Designer. First get count by using AGGREGATOR stage and then do Pivoting.
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / purba
For Datastage:
I/p........sort stage(create key change column for empno.)........ transformer ( here take stage variables count=0,sv1. For sv1 give derivation as(if keychange=1 then sv1=ename else sv1=sv1:',':ename) for count derivation (if keychange=1then count=1 else count=count +1).........remove duplicate stage(retain last)......o/p(desired result)
Is This Answer Correct ? | 1 Yes | 0 No |
Seq stg------>Sort stg--------> Trnsfrm stg-------->Tgt
1. read data in seq file stage.
2. In sort stage enable key column change as true.
3. In transformer stage define stage variable SV1 (if DSLink5.keyChange = 1 then 1 else SV1 +1) and SV2 (if DSLink5.keyChange = 1 then DSLink5.ENAME else SV2 : ',' : DSLink5.ENAME
) . In output tab link ENO, SV2 -> Ename and SV1 -> Count. Add a constraint LastRowInGroup(DSLink5.ENO) and mark the ENO as key in input column.
Is This Answer Correct ? | 0 Yes | 0 No |
Define Merge?
Differentiate between hash file and sequential file?
How to create a doccument in datastage?
how can we validate the flat files using the date in the header and number of records in the flat file? Using both conditions at a time.
how to capture rejected data by using join stage not for lookup stage. please let me know
source file having the data like aabbccc, i want target file result like a1a2b1b2c1c2c3.
parallel jobs run on cluster machines. server jobs run on smp and mpp. What do mean by cluster mechines and SMP and MPP..?
Tell me Wt main advantage of Stage varibles? Project level hints?
Differentiate between Symmetric Multiprocessing and Massive Parallel Processing?
i have a scenario in which i/p columns areID,salary with 1,1000 2,2000 and 3,3000 i need an extra column in the o/p named avg(salary)how can i get it?
detail about sdlc
What is quality stage?