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.
Vertical Pivot
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
SonShe,
Design your job like this:
Make Fld1 the key when reading and writing in the hash file stages.
For the derivation of Field2 put:
This should work.
Tony
Design your job like this:
Code: Select all
HashFile1
|
| Hash_lu
IN V
SeqFile------->XFormer------->HashFile1----->XFormer----->SeqFile2
For the derivation of Field2 put:
Code: Select all
If IsNull(Hash_lu.Fld1) Then IN.Fld2 Else Hash_lu.Fld2 : " " : IN.Fld1
Tony
-
- Participant
- Posts: 85
- Joined: Fri Jun 04, 2004 2:30 am
- Location: Melbourne, Australia
- Contact:
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
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
Cheers,
Dave Nemirovsky
Dave Nemirovsky
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
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
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
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 !!!
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
You may refer the value of previous record in the transformer !!!