I have to split some colums of input data into multiple rows . I searched for this in this forum but pivot is mentioned in all posts which I suppose do not work for variable number columns without any pre-defined upper limit. In other posts its recommended to write a custom routine for this task.
I just wanted to know if there is some other alternative to do it without writing customized routines.
Input:
So here column delimiter is ',' and delimiter for values within colums 4,5&6 is single space(' ').2007-09-21,4162,2,3080 2980 2040 1920 1927,218.4 20.4 117.6 50.4 579.6,0 0 0 0 0
2007-09-21,586,1,4263 4270 171 141 4023 3778 3886 2973 2905 2886,132.0 214.8 288.0 298.8 114.0 50.4 248.4 85.2 19.2 369.6,0 0 0 0 0 0 0 0 0 0
output should be:
So I am choosing one value from col4 and corresponding values from col5 & col6 also. Columns 4, 5 & 6 will always have equal number of sub-columns but number of these sub-columns is variable without any pre-defined upper limit.2007-09-21,4162,2,3080 ,218.4 ,0
2007-09-21,4162,2,2980 ,20.4 ,0
2007-09-21,4162,2,2040 ,117.6,0
2007-09-21,4162,2,1920 ,50.4,0
2007-09-21,4162,2,1927 ,579.6,0
2007-09-21,586,1,4263 ,132.0 ,0
2007-09-21,586,1,4270 ,214.8 ,0
2007-09-21,586,1,171 141 , 288.0 ,0
.......
Please suggest.