I won't post any mapping, I'm not in the mood to do that right now. And I am not in the mood to start my VM now. So you will have to live with a description.
In particular because I don't intend to make life too easy for you. Implementing this following the description below will teach you far more than just copying some mapping and looking at it. Build you own stuff, that's the only way to learn how to build mappings.
What you're asking for is a so-called "de-normalisation process". Basically you do the following steps to build these strings. This approach uses an Aggregator, not an Expression transformation; that's somewhat easier to implement.
First you sort the data by ID. Whether you do that in a relational Source Qualifier (in case the source is a table) or via a Sorter transformation doesn't matter.
Feed the records into an Aggregator transformation. The ID is the Group-By port.
Within this AGG, set up a variable port named v_prev_ID of the same data type as ID with the following expression:
Immediately above this variable port, define another variable port named "v_is_new_ID" (of type Integer) with this expression:
IIF( ID != v_prev_ID, TRUE, FALSE)
You can abbreviate this like this:
ID != v_prev_ID
This works because ports in an EXP or AGG are always processed in a particular order:
- First all input ports from top to bottom,
- next all variable ports from top to bottom,
- last all output ports are set from top to bottom.
In this case v_is_new_ID will be evaluated BEFORE v_prev_ID is set to the current ID, meaning that this comparison is executed with the value of the ID of the previous record in v_prev_ID. That's a very typical trick in PowerCenter to find out whether the current record has the same "ID" as the previous record.
Below v_prev_ID, define a third variable port of type String(10000) named v_all_names with this expression:
IIF( v_is_new_ID, NAME, v_names || ',' || NAME)
That means: if the current ID differs from the ID of the previous record, then set this string to NAME only, otherwise (meaning we're with the same ID as the previous record) append NAME to the current string.
Now define an output port ALL_NAMES of type String(10000) with this expression:
One word of caution:
The trick above works for PowerCenter. In the Informatica platform, you can define whether the Data Integration Service performs certain "optimisations" when executing a mapping. One of these "optimisations" leads to this order (input ports - variable port - output ports) is no longer executed, meaning this trick doesn't work. You have to disable this feature for this mapping. How to do that I can't tell, the Informatica platform is not my area of knowledge. Other people on this forum can tell you.