Vertical Pivot Stage

deepa.y · Post by **deepa.y** » Tue Apr 03, 2012 8:37 am

Hi,
I have the requirement to convert rows to columns.So,i used vertical pivot stage.But it works when the number of records for each key are same.But,my input is as follows.
col1,col2
1,C1
1,C2
1,C3
2,C1
2,C3

So,output should be
1,C1,C2,C3
2,C1,Null,C3
Please suggest me how can i do this in vertical pivot stage

deepa.y · Post by **deepa.y** » Wed Apr 04, 2012 2:17 am

Hi,
I came across the post saying viewtopic.php?t=145198 that it can be done in a transformer using loop variables.Could anyone please give an idea how can i achieve this? and get the following output.
ID,Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8
001,a1,Null,null,null,Null,Null,Null,null
001,a1,Null,a2,null,Null,Null,Null,null
001,a1,Null,a2,a3,Null,Null,Null,null
001,a1,Null,a2,a3,Null,Null,Null,a4

kandyshandy · Post by **kandyshandy** » Wed Apr 04, 2012 3:35 am

Deepa, welcome back again

We have discussed almost something similar in the below link.

viewtopic.php?t=144616&highlight=

I have never heard of Vertical Pivot stage in any DataStage version. There is a pivot stage which can do both horizontal and vertical pivots from version 8.5. Earlier versions can do horizontal pivots only, though vertical can be achieved in transformer stage.

deepa.y · Post by **deepa.y** » Wed Apr 04, 2012 3:45 am

kandyshandy wrote:I have never heard of Vertical Pivot stage in any DataStage version. .

Hi Kandy,
I mean doing vertical pivot in pivot stage only.
Actually the earlier post was to pivot the source data irrespective of number of records.
now,i need to consider maximum number of records (in sample data,it have shown 3.actually it is 45 in my requirement)and insert null if a record is not present while pivoting.

kandyshandy · Post by **kandyshandy** » Wed Apr 04, 2012 5:19 am

Your sample should be more realistic... how do I know that C2 is missing in the following sample? or how do I know that second record is missing for this group?

2, C1
2, C3

deepa.y · Post by **deepa.y** » Wed Apr 04, 2012 5:27 am

From the source i will be getting 3 columns
F_N,element_name,value
1,ID,13
1,Name,xx
1,Place,yy
2,ID,16
2,Place,zz

The maximum number of element_names for key column F_N is 3.
So,output should be
F_N,ID.Name,Place
1,13,xx,yy
2,16,Null,zz

Kryt0n · Post by **Kryt0n** » Wed Apr 04, 2012 3:20 pm

Just thinking out loud as don't know how well it will work...

You could generate x number of rows to match all your possible element_name field values (i.e. id, name, place etc), do an outer join to your source so that each F_N joins to every generated row, this will then ensure every F_N has every possible element. From there do your vertical pivot.

The only hiccup springing to mind is binding the F_N to the generated rows...

kandyshandy · Post by **kandyshandy** » Wed Apr 04, 2012 10:55 pm

That's a nice requirement Deepa

deepa.y · Post by **deepa.y** » Wed Apr 04, 2012 11:02 pm

Thanks Kryt0n.I will try the above logic and let you know.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 05, 2012 12:23 am

Sort and partition by F_N.
In stage variables:

Map ID to svID if element_name is "ID" otherwise leave svID untouched.
Code: Select all
```
svID <== If InLink.element_name = "ID" Then InLink.ID Else svID
```
Map Name to svName if element_name is "Name" otherwise leave svName untouched.

Map Value to svValue if element_name is "Value" otherwise leave svValue untouched.

Use LastRowInGroup() function to constrain the output link. Derive the output columns from the stage variables.

If you have an earlier version than 8.5 follow the Transformer stage with a Remove Duplicates stage, group on F_N and preserve Last.

kandyshandy · Post by **kandyshandy** » Thu Apr 05, 2012 1:44 am

Deepa... Here is something you can try but one additional requirement is highlighted in bold

This is purely from thought process and never been tested.

Sample Input:
1,ID,13
1,Name,xx
1,Place,yy
S,SSSS,SS --> (Add this dummy row to your input file as last row)

Requisites:
Ensure that the records with same F_N is in one partition & ordered.

Stage Variables:
CurrID = Col1
WriteCheck = [If CurrID <> PrevID Then 'WRITE' Else 'DROP']
svPrevCol1 = svCol1
svPrevCol2 = svCol2
svPrevCol3 = svCol3
svPrevCol4 = scCol4
PrevID = CurrID
svCol1 = F_N
svCol2 = [If Col2 = 'ID' Then Col3 Else svCol2]
svCol3 = [If Col2 = 'Name' Then Col3 Else svCol3]
svCol4 = [If Col2 = 'Place' Then Col3 Else svCol4]

Constraint:
@INROWNUM <> 1 and WriteCheck = 'WRITE'

Mapping:

svPrevCol1 -> Col1
svPrevCol2 -> Col2
svPrevCol3 -> Col3
svPrevCol4 -> Col4

All the best

chulett · Post by **chulett** » Thu Apr 05, 2012 6:39 am

Recent versions of DataStage add a mechanism (system variable?) to detect the last record in your input along with some other useful ones. Off the top of my head I don't recall their exact names. This to avoid having to add any kind of 'dummy' row.

qt_ky · Post by **qt_ky** » Thu Apr 05, 2012 8:22 am

Names of the Transformer stage functions are:

LastRowInGroup()

LastRow()

I believe these were first introduced in version 8.5.

kandyshandy · Post by **kandyshandy** » Sun Apr 08, 2012 9:13 pm

Yes Eric

deepa.y · Post by **deepa.y** » Wed Apr 11, 2012 3:47 am

Hi,
This is resolved.I tried the following.

records with same F_N is taken in one partition by selecting appropriate partioning method.

Stage Variables:
next = F_N
ic2=if (next<>prev) then '' else c2
ic1=if (next<>prev) then '' else c1
c1=if ELEMENT_NAME='ID' then VALUE else ic1
c2=if ELEMENT_NAME=Name' then VALUE else ic2
prev=next

Constraint:
LastRowInGroup(F_N)
output columns:
ID=c1
Name=c2
File_Name=F_N