Vertical Pivot

[Ram1234]

Hi All,

I know there are tons of topic's on reverse pivoting but then i didn't get any clear understanding on the solution. Can any please help me out on my issue.

Input :

ID Name
1 John
1 Mike
2 Rita
2 Ray
1 Boy

Output :

ID Name1 Name2 Name3 Name4
1 JOHN MIKE BOY NULL
2 RITA RAY NULL NULL

I guess this can be achieved using stage variable's but not sure how to do...can someone please help me out...

Thanks in advance

[chulett]

Any chance you could use a Server job for this? That would make it trivial. Otherwise you need sorted input & group change detection via stage variables to perform this feet, that and probably a known 'end of data' record added to the stream to pop out the last group.

[Ram1234]

first of all thanks for the reply.....i can not use server job....I can implement it in a lookup in server jobs ...but not sure how to do in parallel jobs

[ray.wurlod]

Sort by ID, generating a key change column.
Based on the key change value, in a Transformer stage either accumulate a delimited list of values or begin a new record.
Downstream of the Transformer stage use a Remove Duplicates stage to preserve only the last of each group.
You're pretty much done at this point. You could write to a CSV file directly. Otherwise you may need to re-parse the data (Column Input stage or Transformer stage) into your arbitrary number of output columns. Of course, the smart approach is to have "sufficient" columns for all cases and populate the remainder after parsing the ones you have with "" or NULL.

[chulett]

Ah, yes... remove duplicates. No need for a silly trailer record. And here I'd just done something similar in that other tool using a Aggregator to capture the last() record from each group.

[Ram1234]

hmm...let me try and get back to you...Thanks a lot for the help