Consider a source which contains the below records:
ID Name
1 A
2 R
3 B
1 D
2 E
2 O
Suppose we want output like this:
ID Name
1 D,A
2 O,E,R
3 B
Above scenario can be implemented in Informatica as depicted below:
Steps:
1) Create source definition which contains the columns named ID and NAME
2) After source qualifier place a sorter and sort the data in ascending order . Here sort key will be ID and sort order will be ascending order.
3) After sorter put an expression transformation . In expression crate following fields in addition to the fields coming from sorter
v_NAME(variable) IIF(ID=v_ID,NAME||','||v_NAME,NAME)
v_ID(variable):ID
out_NAME(output field)=v_NAME
4) After expression place an aggregator . In aggregator group by will be on ID.
5) Then take the ID and NAME field to the target.
