Display with Commas

Solve these with suitable logics in datastage
Input is like..
department_no, employee_name
----------------------------
20, R
10, A
10, D
20, P
10, B
10, C
20, Q
20, S
want result like
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
Show Result like
department_no, employee_list
----------------------------
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, P
20, P,Q
20, P,Q,R
20, P,Q,R,S
want result like
department_no, employee_names
-----------------------------
10, A,B,C,D
20, P,Q,R,S

sai3689
Profile Answers by sai3689 Questions by sai3689
Sep 16th, 2015
3
5557

Questions by sai3689 answers by sai3689

DataStage

Answer

Showing Answers 1 - 3 of 3 Answers

Vijayashree HN

Apr 15th, 2016

Use aggregator stage. Put the group by column as department_no and count column as employee_name.
Propagate count column as employee_list which will gives the number of employs based on department_no.

Vijayashree HN

May 11th, 2016

First sort the columns Department_no and employee_name in ascending order.
Then use the stage variables.
current=department_no
result= If current=prev then result:,:employee_name else employee_name
prev=current
Then use Remove duplicate stage to retain last.
Finally populate the result to target you will get the required output.

Shilpa Banerjee

May 26th, 2016

Use Pivot enterprise stage and select vertical pivoting. Also perform sort in the stage using the partitioning as Hash. Take Dept_number as group by column and Employee_name as Pivot column and the output will be in the form of Dept_number|Employee list. For example:
10|A|B|C|D
20|P|Q|R|S