One row to many

dkuntze · Post by **dkuntze** » Mon Feb 10, 2003 7:31 am

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

chulett · Post by **chulett** » Mon Feb 10, 2003 1:06 pm

You don't mention the version of DataStage you are running, but the 'recent' versions include a Pivot stage. From what I understand, this should do exacxtly what you are looking for.

-craig

ray.wurlod · Post by **ray.wurlod** » Mon Feb 10, 2003 3:28 pm

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