Splitting a Row

[kommven]

Hi I have fields like this in my source

studentID name sub1 sub2 sub3 sub4

I need my target to be populated as

studentID name sub marks



Here the Column name viz Sub1, Sub2, Sub3, Sub4 will populate sub field and corresponding marks will be in marks field.

Every one row will populate 4 rows in target.

Please tell me a solution ASAP

Lucky,

[rasi]

This can be done using Pivot Stage in Datastage. Use the Student Id as the key columns and the marks as derived field as(Sub1,Sub2,Sub3mSub4). This is will create four records for you. There is Pivot Stage PDF document which explains about this stage with example.

Cheers
Siva

[kommven]

I dont have a pivot stage.
I am working on 5.2 version. Alternate solution is appreciated.

Please help

Lucky

[chulett]

Upgrade?

Build them yourself - write out four links from your Transformer stage. Either that or use a big goofy 'union' in your source query to bring four records into the job for every row in the source.

[kommven]

Is there a way, where I can export Pivot Stage from latest Version and use it as custom built stage?

[chulett]

I doubt it. Latest version is 7.5 btw.

You can do it in your version, it's just not automagic. With 'only' four columns to pivot it won't be that bad.

[chucksmith]

The pivot stage is available on 5.2. I used it in many jobs in the past.

Upgrade anyway.

[ray.wurlod]

If it's not installed, you install the Pivot stage (or any other optional ("plug-in") stage using the Package Installer, which is on the server.
You will need your DataStage CD; the software is in a directory called Packages.

There may also be a client component, which you install on your client machine using a regular Windows install.

Within the Packages folder for each stage type, there is also a folder called Docs. In pivot.pdf you will find that one of the examples is almost exactly what you are trying to do (only three pivot columns rather than four).