vertical pivot for variable number of rows and columns
Posted: Sat Jun 18, 2011 8:14 pm
Hi
I have an interesting requirement and I would like to find a systematic solution for it. I would appreciate if you could give me some hints to implement the logic. ( I searched the forum but could not find a good answer)
input file is a sequential file with the format below
zzzzz a b c ...... n
date1 a1 b1 c1 ......n1
date2 a2 b2 c2 ......n2
date3 a3 b3 c3 ......n3
................................
................................
dateX ax bx cx ......nx
I am reading the file as one record as the number of columns is unknown
Number of rows is also unknown as you can guess. in fact the file is comma delimited.
My desired output format will be in a data set of 2 columns
The "zzzz" is garbage and will be thrown out. All the "a" values will be concatenated as well as the "b" and "c" values etc. the date column is same for every row but it is again concatenated with the all the dates in the order of incoming rows.
value ........................ date
--------------------- --------------------------------
a a1 a2 a3 .... an date1 date2 date3 .....datex
b b1 b2 b3 .... bn date1 date2 date3 .....datex
c c1 c2 c3 ...... cn date1 date2 date3 .....datex
......................... ........................................
......................... ........................................
n n1 n2 n3 .....nx date1 date2 date3 .....datex
I am able to handle the date part easily, the value part requires vertical pivot with unknown number of columns which I think is not possible.
I have an interesting requirement and I would like to find a systematic solution for it. I would appreciate if you could give me some hints to implement the logic. ( I searched the forum but could not find a good answer)
input file is a sequential file with the format below
zzzzz a b c ...... n
date1 a1 b1 c1 ......n1
date2 a2 b2 c2 ......n2
date3 a3 b3 c3 ......n3
................................
................................
dateX ax bx cx ......nx
I am reading the file as one record as the number of columns is unknown
Number of rows is also unknown as you can guess. in fact the file is comma delimited.
My desired output format will be in a data set of 2 columns
The "zzzz" is garbage and will be thrown out. All the "a" values will be concatenated as well as the "b" and "c" values etc. the date column is same for every row but it is again concatenated with the all the dates in the order of incoming rows.
value ........................ date
--------------------- --------------------------------
a a1 a2 a3 .... an date1 date2 date3 .....datex
b b1 b2 b3 .... bn date1 date2 date3 .....datex
c c1 c2 c3 ...... cn date1 date2 date3 .....datex
......................... ........................................
......................... ........................................
n n1 n2 n3 .....nx date1 date2 date3 .....datex
I am able to handle the date part easily, the value part requires vertical pivot with unknown number of columns which I think is not possible.