Multiple rows from different sources into single row

[richieRich]

Hi all,

I am just a data stage beginner. I've been searching on the forums on whether it's possible to merge multiple rows from different sources into one row. So far the pivot stage seems to be the closet thing to what I want but I'm still not sure it will meet my requirements. Let me explain.

I have a stream of data in my parallel job that starts off at an oracle stage. It returns three rows. From the oracle stage I have three transformers. Each to handle the three different types of data.

The output columns of the first transformer is the following columns...
Member no, member valid

The output of the second transformer is ...
Staff no, staff valid

The output of the third transformer is ....
Valid card

Normally you would end up with three output rows but My question is how can I end up with one row which combines the content of the three rows...

Member no, member valid, Staff no, staff valid, Valid card

All these rows do have a transaction key in common which I can use if required. What stage would best be suited for what I want?

Thanks for your help.

[prasson_ibm]

Hi,

You can use join stage of type inner and your joining key will be transaction key.

[richieRich]

But the trick with my scenario is that all three transformers lead back to the same oracle enterprise stage. So the rows coming out of the transformers come out one by one. I.e. I want to read all the rows then after all rows are read combine output as originally described.

For a join to work you need rows coming from each input link at the same time right?

[richieRich]

So to summarize it better I guess what I'm trying to do is convert multiple rows with non repeating columns into a single row with multiple columns. Not just a single row with 1 column.

[asorrell]

I believe the Merge stage is what you are looking for. It can take multiple inputs and Merge them based on a key. One data input will have to be designated the "master" and the others are matched up against it. All three inputs should be sorted and partitioned on the transaction key.

[richieRich]

Thank you so much for your advice. This worked for me.