DSXchange

I have data in multiple rows that I want to combine as multiple columns in a row. While the 'combine records' worked and did combine the rows into the subrec column, I do not know how to split out this subrec to populate into multiple columns in a row.

Here is an example of my input data before 'combine record':

key data
1 aaaa
1 bbbb
1 cccc
1 dddd

After 'combine record' the view from the dataset looks like this:

key subrec
1 [() () () ()]

What stage do I use to name each section of the subrec? I've tried 'split vector', 'split subrecord' and transform without success.

p.s. I do not see a 'Vertical' option on the PIVOT stage. It is only for horizontal pivot.

Any suggestion that would make this work would be appreciated.

PTL

Welcome aboard.

Please post the record schema of your output link. Are you using runtime column propagation?

You can use stage vaaiables in Transforme to implement this logic. Compare the currect record with previous record and concate the value in one column till keys are same and then you can use Column import and remove duplicate stage to remove duplicate record and retain the last record..

Sorry for replying to such an old post . But I've verified if you define the nested level columns ( level 02 columns of sub rec) in the combine subrecords stage , it works perfectly , and would be pretty useful to do vertical pivoting in conjunction with a column export stage - especially with older versions of datastage ( < v8.5) . The key is to define metadata correctly - especially the vector occurs property ( I tested with variable length vectors) . The Col Export stage is fussy about specifying the vector prefix for a variable length vector field.