Vertical Pivot

[SonShe]

Before I post let me ask for apology for repeating this question as this has already been discussed on this forum. I still can't figure out without spending some time. I wish if I can get the code if someone has already done this saving me the efforts.

I have multiple rows of input as follows:

Fld1 Fld2
----- -----
A X01
A X02
A X03
B Y01
B Y02
B Y03

The output to like as under

A X01 X02 X03
B Y01 Y02 Y03

Please reply. Thanks a lot in advance.

[tonystark622]

SonShe,

Design your job like this:

Code: Select all

               HashFile1
                   | 
                   | Hash_lu
          IN       V
SeqFile------->XFormer------->HashFile1----->XFormer----->SeqFile2

Make Fld1 the key when reading and writing in the hash file stages.

For the derivation of Field2 put:

Code: Select all

If IsNull(Hash_lu.Fld1) Then IN.Fld2 Else Hash_lu.Fld2 : " " : IN.Fld1

This should work.

Tony

[sim]

How would I do this vertical pivot in DataStage 7 Parallel Extender? Any design help...what stages to use? Thanks

[davidnemirovsky]

I would just use a simple SQL statement like the following:

select ID, MAX(Fld1) "Fld1", MAX(Fld2) "Fld2"
from
(select ID,
case when Colname = 'Fld1' then New_Value else NULL end "Fld1",
case when Colname = 'Fld2' then New_Value else NULL end "Fld2"
from your_table
)
group by ID

[sim]

I have data in a sequential file. My data look like this:

25UB|23435
25XZ|89435
25MB|46377
25MD|38204
25YN|32048

I want to convert this data to:
25UB|23435|25XZ|89435|25MB|46377|25MD|38204|25YN|32048

My environment: DataStage 7.1 Parallel Extender in Unix server

Thanks

[him121]

u can do this same way as tony answer quesion of Sonshe...

u need to pass this seq file and do the lookup...and pass the data to same hash file.

in the transformer....
you can easily give the condition with lookup...

like
stagevar=If isNull(Lookup.Fd1) then '' else Lookup.Fd1

in Tranforrmer

stagevar:'|':SourceSeq.Fd1:'|':SouceSeq.Fd2

[rasi]

Why not use the power of awk, sed in unix....

to do


Thanks
Siva

[cyh]

I have the same problem before.

From my experience, the problem is much simple if there are only a few alternative values for Fld1. Let's say Fld2 can be '01','02','03','04' only.

You can use following flow :

Input -> Sort -> Transformer -> Output

Code: Select all

Fld1   Fld2     Fld1 Fld2a Fld2b Fld2c Fld2d
-----  -----    ---- ----- ----- ----- -----
A       01 ->    A    01 
A       02 ->    A          02
A       03 ->    A                03
B       01 ->    B    01
B       02 ->    B          02
B       03 ->    B                03

                        |
                        V

                Fld1 Fld2a Fld2b Fld2c Fld2d
                ---- ----- ----- ----- -----
                 A    01    02    03
                 B    01    02    03

The myth here is that you hold the value of Fld2 in stage variable of the transformer, where the key field is change, output the value.

You may refer the value of previous record in the transformer !!!