String Aggregation In Informatica

String Aggregation using Informatica:

Source Data:

Birth Year            Name

1987                       John

1987                       Kelly

1988                       Tom

1988                       Bill

1987                       Bob

We need the result set as follows:

Birth Year            Names

1987                       John,Kelly,Bob

1988                       Tom,Bill

                               

Following steps need to be followed in Informatica:

1.       After the SQ object use an Aggregator

2.       In the aggregator sort by the Birth Year column

3.       After the aggregator transformation, use an expression transformation

4.       In the expression we should have the following ports:

a.       In_Birth_Date

b.      In_Name

c.       v_Names= IIF(In_Birth_Date = v_Birth_Date,v_Names||’,’||In_Name,In_Name)

d.      v_Birth_Date = In_Birth_Date

e.      Out_Names=v_Names

5.       Take the last concatenated string to output to the target

6.       This will give you the desired result.