If it is only ten columns. Try this in a transformer
In the inut column you should have two columns one should be key and other one is the column to be pivoted. Add zeros to input colmns and sort it in descending order
Stage Variables:
Source => Ans0:Space(5-Len(Ans0)):Ans1:Space(5-Len
(Ans1)):Ans2:Space(5-Len(Ans2)):Ans3:Space(5-Len
(Ans3)):Ans4:Space(5-Len(Ans4)):Ans5:Space(5-Len
(Ans5)):Ans6:Space(5-Len(Ans6)):Ans7:Space(5-Len
(Ans7)):Ans8:Space(5-Len(Ans8)):Ans9:Space(5-Len(Ans9))
KeyChange => If @INROWNUM=1 then 0 else If
Input_Lnk.KEYColumn= Currentkey then 0 else 1
Ans0 => 0
Ans1 => 1
.
.
.
Ans9 => 9
PrevKey => CurrentKey
CurrentKey => Input_Lnk.KeyColumn
[b]In out_Link => Constraint : KeyChange=1[/b]
Key_Column => PrevKey
Column1 = > Trim(Source[1,5])
Column2 => Trim(Source[6,5])
.
.
.
Till last column
Vertical pivoting
Moderators: chulett, rschirm, roy
-
vcsasikala
- Participant
- Posts: 26
- Joined: Wed Jun 20, 2007 1:13 am
- Location: Chennai
If it is only ten rows, then you may try the following. Use as many stage variables for as many rows you have.
Use 10 Stage Variables for this case. named S1, S2, S3 ... S10
1st Row
S1=If @INROWNUM=1 then SOURCE else S1
2nd Row
S2=If @INROWNUM=2 then SOURCE else S2
and so on
For each row, assign all these stage variables to the output columns. Now you will have 10 rows as output. The last row will have all the stage variables values. Use remove dup stage and take the last row. Hope this may be useful.
Use 10 Stage Variables for this case. named S1, S2, S3 ... S10
1st Row
S1=If @INROWNUM=1 then SOURCE else S1
2nd Row
S2=If @INROWNUM=2 then SOURCE else S2
and so on
For each row, assign all these stage variables to the output columns. Now you will have 10 rows as output. The last row will have all the stage variables values. Use remove dup stage and take the last row. Hope this may be useful.
If it is only ten rows, then you may try the following. Use as many stage variables for as many rows you have.
Use 10 Stage Variables for this case. named S1, S2, S3 ... S10
1st Row
S1=If @INROWNUM=1 then SOURCE else S1
2nd Row
S2=If @INROWNUM=2 then SOURCE else S2
and so on
For each row, assign all these stage variables to the output columns. Now you will have 10 rows as output. The last row will have all the stage variables values. Use remove dup stage and take the last row. Hope this may be useful.
Use 10 Stage Variables for this case. named S1, S2, S3 ... S10
1st Row
S1=If @INROWNUM=1 then SOURCE else S1
2nd Row
S2=If @INROWNUM=2 then SOURCE else S2
and so on
For each row, assign all these stage variables to the output columns. Now you will have 10 rows as output. The last row will have all the stage variables values. Use remove dup stage and take the last row. Hope this may be useful.
