How to Achive bellow logic in datastage

[aladap12]

have my Input data like


ID NUM CODE

1 5 SAM1
1 5 SAM2
1 5 SAM3
1 4 SAM4
2 7 REC1
2 7 REC2
2 7 REC3
12 8 REC1
13 9 REC2
17 10 REC22
17 10 RECDDD
20 56 ACD

In the output i need to display data like


ID CODE
1 5| SAM1| SAM2|SAM3
1 4|SAM4
2 7|REC1|REC2|REC3
12 8|REC1
13 9|REC2
17 10|REC22|RECDD
20 56|ACD

[kaps]

Use Transformer and Remove duplicate stage. In Transformer, have stage variables to store previous values for code and key values. sort the input.
Whenever keys are same keep appending to the stage variable. In remove dupliate stage get the last record from the group.

[greggknight]

Try this stage

PxCombineRecords Stage

Combine records with identical keys into single record
Data restructuring stage for Parallel Extender jobs, which combines rows in which particular key column's values are identical into vectors of subrecords.

Or
If you are using 8.5 use the new functionality (looping) with the functions lastrowingroup and so on.

[ray.wurlod]

Search DSXchange for "vertical pivot" for that is precisely what you are seeking to accomplish.

[Rob4732]

I have used Kaps recommendation in this thread(using staging vars and then remove dupes). I noticed a reference in this thread to using the loop construct in a transformer stage in 8.5. Everything I have read so far indicates the xfm loop allows one to split a single row into many or add an aggregate value to each row. I don't see a reference to combining multiple rows into 1. I will do some more research, any suggestions appreciated tough.

Thanks

Robert

[Rob4732]

I just noticed in Greg Knights post, he also mentions using the funciton lastrowingroup to achieve a vertical pivot. My bad. Bad Robert

[ray.wurlod]

Maybe simply the fact that the data are all upper case means that "bellow logic" has already been achieved!