Source Like department_no, employee_name ---------------------------- 20,

Source Like
department_no, employee_name
----------------------------
20, R
10, A
10, D
20, P
10, B
10, C
20, Q
20, S

and Output should be like this

department_no, employee_list
--------------------------------
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, A,B,C,D,P
20, A,B,C,D,P,Q
20, A,B,C,D,P,Q,R
20, A,B,C,D,P,Q,R,S

Question Posted / prasad

3 Answers
9357 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Source Like department_no, employee_name ---------------------------- 20, R 10, ..

Answer / subhash

small modification to the above ans:
SRC---->Transformer--->TGT
we do the 'HASH' partitioning on Dept_No.

SetNull value to the Stage variable----->SV_List

In the transformer stage variables:
SV_List :',' : Employee_Name ----->SV_List (Concatenating Employee
Names)

In the Column Derivation:
TRIM(SV_list, ',' , 'L')------->Employee_Name

Is This Answer Correct ?

5 Yes

1 No

Source Like department_no, employee_name ---------------------------- 20, R 10, ..

Answer / srinivas

By using the stage variables in transformer we can do this.

Before going to use transformer sort the data based on two columns.Order should be like below

Deptno Empname
10 A
10 B
SO ON...

After that in transformer take 3 stage varibles

stg1--->map the sorted Deptcolumn

stg2---> DSLink2.empname:',' (map the empname column and concatenate comma operator)

Stg2--->stg3:stg2 (concatenate first two staging varibles)

Then

In derivation of employee_list column (In transformer)

use below function

TRIM(stg3,',','T')---->employee_list (column derviarion)

It will gives you as expected result.

Thanks
Sri

Is This Answer Correct ?

5 Yes

3 No

Source Like department_no, employee_name ---------------------------- 20, R 10, ..

Answer / subhash

SRC---->Transformer--->TGT
we do the 'HASH' partitioning.

SetNull value to the Stage variable----->SV_List

In the transformer stage variables:
SV_List : Employee_Name ----->SV_List (Concatenating Employee
Names)

In the Column Derivation:
SV_list------->Employee_Name

Is This Answer Correct ?

3 Yes

1 No

Post New Answer