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
Code: Select all
col1 a b c d e
1 x y z
2 l m
3 h i j k l
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)
Do you see any issues with this approach?