split one row to multiple row in oracle

Question

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.

You can check this - stackoverflow.com/questions/20269917/… — Shankar
– Shankar, Commented Jun 14, 2016 at 19:18
Please search stachoverflow next time before you post a question, you'd be amazed how many times the same question is asked by different people — Shankar
– Shankar, Commented Jun 14, 2016 at 19:19
sorry, my english is not good, i have hard time to find the keyword for googling it, thanks — toMuchArgs
– toMuchArgs, Commented Jun 14, 2016 at 19:28
i dont understand it, so i simply copy paste it in my toad and it show error PLS-00103 encountered the symbol @ — toMuchArgs
– toMuchArgs, Commented Jun 14, 2016 at 19:46

krokodilko · Accepted Answer · 2016-06-14 19:45:21Z

1

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.

edited Jun 14, 2016 at 19:45

answered Jun 14, 2016 at 19:33

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

toMuchArgs Over a year ago

that solve my problem, you're my hero, for now, thank you

Collectives™ on Stack Overflow

split one row to multiple row in oracle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related