CODEWITHSUNDEEP
sqloracle-databaseoracle11g
toMuchArgs
toMuchArgs

Reputation: 53

split one row to multiple row in oracle

I have a simple select query that has this result:

first_date | last_date  | outstanding
14/01/2015 | 14/04/2015 | 100000

I want to split it to be 

first_date | last_date  | period     | outstanding 
14/01/2015 | 31/01/2015 | 31/01/2015 | 100000 
01/02/2015 | 28/02/2015 | 28/02/2015 | 100000
01/03/2015 | 31/03/2015 | 31/03/2015 | 100000
01/04/2015 | 14/04/2015 | 31/04/2015 | 100000

Please show me how to do it simply, without using function/procedure, object and cursor.

Upvotes: 0

Views: 66

Answers (1)

krokodilko
krokodilko

Reputation: 36087

Try:

WITH my_query_result AS(
  SELECT date '2015-01-14' as first_date , date '2015-04-14' as last_date,
         10000 as outstanding
  FROM dual
)
SELECT  greatest( trunc( add_months( first_date, level - 1 ),'MM'), first_date ) 
           as first_date,
        least( trunc( add_months( first_date, level ),'MM')-1, last_date ) 
           as last_date,
        trunc( add_months( first_date, level ),'MM')-1 as period,
        outstanding
FROM my_query_result t
connect by level <= months_between( trunc(last_date,'MM'), trunc(first_date,'MM') ) + 1;

A side note: April has only 30 days, so a date 31/04/2015 in your question is wrong.

Upvotes: 1

Related Questions