Sunday 4 September 2011

How to concatenate values from multiple rows in Informatica

In Informatica , many times we face scenario where we have to concatenate values from two or more rows into a single row.

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.

No comments:

Post a Comment