Hi,
I'm looking for a DataStage internal solution to this problem:
I have got a source file with semicolon separated columns like this:
A;B;Z,D,K
H;U;I,O,P,L,K
The third column is separated by comma internally.
The expected output looks like this:
A B Z
A B D
A B K
H U I
H I O
and so on.
Is it possible to do this job using a transform or routine ?
Or should I write a filter program to transform the source outside of DataStage ?
Thank you for your suggestions.
Detlev
One row to many
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I assume the fifth row of your expected output should be H U O, in which case the Pivot stage is a perfect match. Prior to the Pivot stage you will need a Transformer stage to split the comma-separated values into separate columns, and - given that there may be a variable number of these - a later constraint to eliminate those rows in which there is no value (or "", or whatever your design dictates).
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518