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 query.

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.

For example.

select
     x.id,
       x.startDate,
       x.startDate + (p.position - 1) as att_date,
       substr(x.delimedString, (p.position * 2) - 1, 2) as val,
from   table1 x,
       (select rownum as position
        from   dual
        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.