Create Pivot Horizontal table

[Pikty]

Hi there,

I'd like to use a pivot table to convert a vertical table into a horizontal table
Basically I'm receiving data as below:

ID Name Value
1 aaa 123
1 aaa 456
1 aaa 789
2 bbb 963
2 bbb 852
3 ccc 111
4 ddd 444
4 ddd 555
...


and I would like to convert the data as below:

ID Name Value1 Value2 Value3 Value4
1 aaa 123 456 789
2 bbb 963 852
3 ccc 111
4 ddd 444 555



Please could you help and point me to the right direction?
Appreciate
Pik

[chulett]

Well... start by looking at the Pivot stage as that's exactly what it does.

[Pikty]

True... just looking for a working sample. to configure it properly

by the way I'm running on Datastage 8.1
I ran couple of tests and I'm not able to dispatch the result as expected...

[chulett]

Details, please. How did you configure it and what did the output look like?

[Pikty]

Sure, please have a look below

In the Inputs tab:
I have 2 columns:

columnname
ID (Key)
MyNumbers

In the Outputs tab
columnname Derivation
ID (Key)
Value1 MyNumbers
Value2 MyNumbers
Value3 MyNumbers

and here is the result

Peek_429,0: persIdf:aaa Value1:47650 Value2:47650 Value3:47650
Peek_429,0: persIdf:aaa Value1:47620 Value2:47620 Value3:47620
Peek_429,0: persIdf:aaa Value1:47640 Value2:47640 Value3:47640


Thanks

[prasson_ibm]

Hi,

Your example is for Vertical Pivot.Vertical Pivot stage is working in Datastage 8.7.Your example has dynamic changes in grouping of key(ID) records,so i am not sure if it will work with Pivot Stage but you can try.

In Pivot Stage:-
Define ID column as Group By column in Stage tab,

Define Value column as Pivot and give array size to 3.

In output tab,you will get Value_1,Value_2 and Value_3.

Thanks
Prasoon

[Pikty]

Hi Prasoon,
These options are not available for 8.1 unfortunately.
I guess I'm stuck and have to use stage variables to make it work.

[rameshrr3]

Pretty easy if your data is sorted. You need at most a sort stage for unsorted data , with key change indicator pseudocolumn , us ethis in a transformer to concatenate strings if key col data is same, and use remove duplicates at the end with duplicate to retain = last .

[srinivas.nettalam]

After the above process pass the data to a column import stage and split the column's data to multiple columns or use a transformer field function whichever is effective with your volume of data..

[Pikty]

Thanks guys. I'll move in that direction then