Dynamic Vertical Pivot

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
jerome_rajan
Premium Member
Premium Member
Posts: 376
Joined: Sat Jan 07, 2012 12:25 pm
Location: Piscataway

Dynamic Vertical Pivot

Post by jerome_rajan »

Hi,

I am faced with a situation where we may need to vertically pivot data from rows to columns but do not know for sure exactly how many columns it will need to be pivoted to.
We do have an upper limit but the limit is 100 and the probability of getting more than 8-12 columns is very less. We want to add additional columns only if we start receiving the corresponding values in the source data. Thus the need to dynamically pivot data.
I have thought of a design and would like to know your thoughts on this.

Input data(vertical)

Code: Select all

col1 col2 col3 
1      a     x
1      b     y
1      c     z
2      a     l
2      b     m
3      a     h
3      b     i
3      c     j
3      d     k
3      e     l
Output expected is

Code: Select all

col1 a b c d e
1     x y z
2     l m 
3     h i j k l
My proposed design:

Code: Select all

Read input data -->Transformer -------------->  Column Import ------RCP------->Oracle
                            (with keychange                 (parse the delimited
                              logic and all values            column into multiple
                              pivoted into delimited        columns whose definition 
                              list in a stage variable.      is defined in a schema file 
                              The last row outputs the    )
                              keys and the value of the 
                              sv)
The only change required would be to the schema file.

Do you see any issues with this approach?
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn

Life is really simple, but we insist on making it complicated.
Post Reply