Food for thought - Pivoting

[Django]

Hello Ds Guruz

I need help to pivot a seq. file with 2 columns to another seq. file with 2 columns.

Code: Select all

Input Sequential File 
------------------
Name    Score
------------------
Ramyt   1,2,3
Renai    5,6,7,8,9,10,345,122,456
Scott     345,67,23,1,8,4,45,89,23,78,90,100,60,23,12
Brian     23,45
etc

The length of the score is varying comman seperated list

I want the output as follows

------------------
Name    Score
------------------
Ramyt  1
Ramyt  2
Ramyt  3
Renai   5
Renai   6
Renai   7  
and so on

How to achieve this ?

Any idea ?

[ray.wurlod]

Is there a comma between the name and the list of scores? If so read the file as comma delimited and run it through a Pivot stage. If not, convert it to this format (maybe with a Transformer stage), re-parse it (maybe with a Column Input stage) and then run it through a Pivot stage.

[Django]

Ray

I understand your point of pivoting. Assuming that we make the comma seperated list into columns. But the issue is the the number of input columns are varying. It can be 1 upto say 200 or more. I dont want to map into 200 columns..

[ray.wurlod]

Why not? You're expecting to get 200 rows in this case. The columns will only be visible within DataStage, nowhere else.

If you were prepared to use a server job, you could use the multi-value handling and dynamic normalization capability of a hashed file to solve this for an arbitrary number of columns per row with no programming at all but, since you posted in the parallel forum, I conclude that this is not what you wish to do.