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
Answer Posted / 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 |
Post New Answer View All Answers
Hi, what is use of Macros,functions and Routines..? At what situation you are used. If you know the answer please explain it. Thanks.
in oracle target stage when we use load option and when we use upsert option?
What are the types of containers and how to create them?
How many Key we can define in remove duplicate stage?
What are sequencers?
Explain connectivity between datastage with datasources?
What are the various kinds of the hash file?
Can you explain kafka connector?
Explain datastage architecture?
Different ways a project can be moved to production ?...example ….export-import and Version control.
how to connect source to db?generally what r stages u used? how to find the data is having delimiter format?
How do u convert the columns to rows in datastage?
How to find value from a column in a dataset?
How a server job can be converted to a parallel job?
What is difference between symmetric multiprocessing and massive parallel processing?