DSXchange

Hello,

Input file comes as follows.

1,STREET,ABC
1,ADD1,
1,ADD2,00
1,CITY,NYC
1,STATE,NY
1,COUNTRY,US
2,STREET,blvd
2,ADD1,1234
2,ADD2,add2
2,CITY,NW
2,STATE,NJ
3,STREET,
3,ADD1,new1
3,CITY,PHIL
3,STATE,PA
3,COUNTRY,US

Output file should look like this

Output Column Names: NO,STREET,ADD1,ADD2,CITY,STATE,COUNTRY
1,ABC,,00,NYC,NY,US
2,blvd,1234,add2,NW,NJ,
3,new1,,PHIL,PA,US

Help Appreciated.

That would be a "vertical pivot" and there's no stage to automate that for you. However, an exact search here for vertical pivot will reveal... well, lots of post telling you to search and the occassional one that explains your options. Short version, two main options:

1) Do the assembly in a stage variable and then write out the whole record after you tack on the last field

2) Use a hashed file to assemble each record, keyed by that NO field and adding field values on every successful lookup. Dump the hashed file when you are done.