Query to split one row to multiple rows.

I have records like below.

col1      col2
11-Sep-14       11-Oct-14
15-Sep-14 18-Sep-14

In the first row I am having col1 & col2 in different months, so first record should be split like below

col1                      col2
11-sep-14          30-sep-2014
1-oct-14             11-oct-2014


In the second row, I just have values from same month,
so it should as it is. so altogether it should be 3 rows.
sakthikumarAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
SELECT GREATEST(col1, ADD_MONTHS(TRUNC(col1, 'mm'), COLUMN_VALUE)) col1,
       LEAST(col2, LAST_DAY(ADD_MONTHS(TRUNC(col1, 'mm'), COLUMN_VALUE))) col2
  FROM yourtable,
       TABLE(
               SELECT COLLECT(LEVEL - 1)
                 FROM DUAL
           CONNECT BY ADD_MONTHS(TRUNC(col1, 'mm'), LEVEL - 1) <= TRUNC(col2, 'mm')
       )
0
 
sakthikumarAuthor Commented:
Hi SDSTUBER,

This query works exactly for me, just like to know if I achieve this through outer joins...

just like to have your expert opinion on this.
0
 
sdstuberConnect With a Mentor Commented:
There are probably some special cases where you could make an outer join work, but as a general technique for this kind of row expansion, no outer joins aren't really a viable option.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.