eno ename 1 qaz 1 wsx 1 edc 2 zxc 2 asd 3 qwe 3 wer 3 tru 4 rgj

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.

Question Posted / jeetenk

4 Answers
8307 Views
Wipro, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

eno ename 1 qaz 1 wsx 1 edc 2 zxc 2 asd 3 qwe 3 wer 3 tru 4 rgj Output: eno e..

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

eno ename 1 qaz 1 wsx 1 edc 2 zxc 2 asd 3 qwe 3 wer 3 tru 4 rgj Output: eno e..

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

eno ename 1 qaz 1 wsx 1 edc 2 zxc 2 asd 3 qwe 3 wer 3 tru 4 rgj Output: eno e..

Answer / msbharathi

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

eno ename 1 qaz 1 wsx 1 edc 2 zxc 2 asd 3 qwe 3 wer 3 tru 4 rgj Output: eno e..

Answer / ajajsp

select eno, ename, count(*) from Table_Name group by eno, ename

Is This Answer Correct ?

1 Yes

2 No

Post New Answer