convert column data in to 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
cherry
Participant
Posts: 108
Joined: Sun Jul 10, 2005 1:35 am

convert column data in to rows

Post by cherry »

Hi All,

I have the below scenario and my pinput data is as below

Input
-----
Id, Name
1, r;b;c
2 a;d;g;h;j

Name field has data which is ';' delimited but it is one record

I would like my input to be as shown below:

Output
------
Id, Name
1,r
1,b
1,c
2,a
2,d
2,g
2,h
2,j

Any help here please.

Thanks
Cherry
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Column Import stage to split the delimited field into columns then a Pivot stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
cherry
Participant
Posts: 108
Joined: Sun Jul 10, 2005 1:35 am

Post by cherry »

Hi Ray,

The name field is not fixed with number of delimiter's say for one record it might have 5 delimiters and other row would have more than 5. In this case iam not sure if column import stage will work.

Thanks
Cherry
bhasds
Participant
Posts: 79
Joined: Thu May 27, 2010 1:49 am

Post by bhasds »

Hi Cherry,

This may be possible through loop variable in transformer stage.

1.In stage variable-
Name SV1
2. In loop variable-
Loop condition-

Code: Select all

@iteration<=DCount(SV1,";")

Code: Select all

Field(SV1,";",@iteration)             LV1
3.In derivation map the ID directly from input and the take the output of LV1 for Name.

Please let me know if I am wrong.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Do you know the maximum number of delimited values? If so, code for that.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply