Concatenate the rows

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
toscajo1103
Participant
Posts: 3
Joined: Thu Dec 18, 2014 7:37 am

Concatenate the rows

Post by toscajo1103 »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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?
-craig

"You can never have too many knives" -- Logan Nine Fingers
toscajo1103
Participant
Posts: 3
Joined: Thu Dec 18, 2014 7:37 am

Post by toscajo1103 »

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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
toscajo1103
Participant
Posts: 3
Joined: Thu Dec 18, 2014 7:37 am

Post by toscajo1103 »

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.
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

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.
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
Post Reply