DSXchange

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

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

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...

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

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

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

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.

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 .

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..

Thanks guys. I'll move in that direction then