Converting Column to Rows

[parag.s.27]

Hi,

I have a requirement where in one column I am getting certain values for different records. I have to extract this column and convert the values into rows.

For e.g My record is as: -

Code: Select all

AD|BANK OPS|255068|1098765|BC|46E;654;87R

Here the last column has multiple values seperated by ";". What I need is as follows: -

Code: Select all

46E
654
87R

.

The number of ";" seperated values for different records may change and are dynamic. In DS Server 7.5, since there is no Pivot stage, can anyone please help and suggest how to do this?

[Sainath.Srinivasan]

Pivot stage is part of default DataStage installation.

Btw, do you want the last column alone or all other columns with the last one made single?

[parag.s.27]

Pivot stage is part of default DataStage installation.

Btw, do you want the last column alone or all other columns with the last one made single? ...

Hi,

I needed only the last column...

Here I meant that in DS 8.1 there is Pivot enterprise stage where as in Server or 8.0 there is Pivot stage that works on columns, but here there is only a single column. I though of first seperate the last column then read it with ";" as a delimiter and then use pivot. But this will not work because some record may have 2 values and some may have 15.

Anyways, I've resolved it now and done a very simple trick with the ascii codes. So using a single transformer I've converted the values in that column to rows.

[parag.s.27]

I simply replaced the ";" with Char(10) and wrote the data in Seq file ina UNIX New Line mode.