DSXchange

I am trying to do something similar to this post... find a way in DataStage to do the following:

Output from an ETL job creates a file where each row looks as:

ID, date period, Type1, Totala, Totalb
ID, date period, Type2, Totala, Totalb
ID, date period, Type3, Totala, Totalb


I like to have each row to look like this:


ID, date period, Type1, Totala, Totalb, Type2, Totala, Totalb, Type3....


I am extremely new with DataStage so reaching out to the Gurus here. Help is really really appreciated.

Welcome aboard... a couple of things.

First, I've put you into your own post and linked back to the 'similar' thing you are trying to do. Secondly, this meant I had to guess at some things, can you clarify for us:

1) Your DataStage version
2) Your server Operating System

Thanks.

I'd also be curious what you've tried, any kind of pivoting that the post you found mentioned?

Thanks for setting me up.

DataStage version is 8.0
Windows
Linux\DB2

I tried playing with pivot transformer. Not sure if its this version but it doesn't seem to work.

Looks like you just want to accumulate source columns into a single row until the key changes?

Is the number of source rows per key constant or variable?

If you are saying source meaning 'Type' then yes, each row has multiple Types. Starting out with 10 types.

So it would be constant initially. As Types get added this too will need to increase going forward.

It can be achieved in newer versions with vertical pivot or looping but there functionality is not available in 8.0. Do a search on vertical pivot and look for some answers for pre 8.5 version. Should also be achievable with stage variables but a little tricky to identify the last record.