Again something i struggled with myself and
found no help with online.
We will need to use a clever little function in oracles bag of tricks, the
Hierarchical queries allow you to create a query on a table where the query
will link back to the same table any number of times. I wont go too in depth
about these themselves as their are some quite good webpages explaining them
that are easily found on google.
How do these queries allow you to change a delimited column into multiple rows?
Well we are miss using the function really, it is not truly intended for this
use but the function lends it self very nicely to this situation.
There are plenty of examples on oracles forums (if you can pick through them)
of using this type of query to great effect on character delimited strings. On
implementing against actual data and adapting to a fixed length rather than
delimited i found a lot of problems. Effectively once we took the examples
beyond one concatenated string. The results became geometric, growing by a
factor of the original row number with every iteration. So an initial sample of
3 rows with delimited columns would give 9 rows after just 3 iterations.
The solution was quite simple, as we have fixed length data (in my case 366
sets of 2 characters,) instead of using the actual data in the hierarchical
query we can simply use the hierarchical query to create a table to which we
link our data. this table will hold sufficient information to allow a simple
select to filter the data to what we need.
x.startDate + (p.position - 1) as
substr(x.delimedString, (p.position * 2) -
1, 2) as val,
from table1 x,
(select rownum as position
connect by level < 366) p
where p.position <= (length(x.delimedString) / 2)
As you can see we have made every row in table1 into 365 rows. These rows will contain all of the information plus a rownum value. We use this rownum value in the select clause, to make each val value different. So the row with position 1 will take the first 2 charecters. The row with position as 3 will take the 5th and 6th numbers.