# 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

#### 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