A lookup transformation configured as an Unconnected Lookup can only return one port. However, there are times when you need to quickly modify a mapping to return more than one port from the unconnected lookup.
There are two ways to do this:-
1. To convert the unconnected lookup to a connected lookup in the pipeline and then take out all the required ports
2. To use Lookup Sql query override to concatenate multiple columns with a common delimiter and return the concatenated value.
The second option is like a hack and saves your time required to change the configuration of a lookup from unconnected to connected.
However, a point to remember is that it only works if the lookup is on a database table. In case of lookups on flat files the sql query override option is not available.
Once you concatenate multiple fields using sql query override you can again separate them out in the calling expression transformation by using substr and instr functions of Informatica and assign the resulting values to individual ports or variables.
Hope this helps and do let me know if you need more details on this technique.
can you post the screenshot for this specially of the concatenation and use of substr and instr functions