oracle sql!!!

[rajprakashraj]

i have a start date- 1-Jan-2017 with 100 dollars.

i need to write sql to generate 12 records like

col A              col B

1-jan-2017      100

1-feb-2017

1-mar-2017...till 1-dec-2017 for that whole year. 

 

Its not saved in db, i need to generate report based start date

[nenu_devudni]

--
with src as
(
        select '01-JAN-2017' col1, '100' col2 from dual union all
        select '01-FEB-2017',      '100'            from dual union all
        select '01-MAR-2017',      '100'         from dual union all
        select '01-APR-2017',      '100'   from dual union all
        select '01-MAY-2017',      '100'            from dual
)
, explode as
(
        select  col1
        ,       regexp_substr(col2, '\w+', 1, 1) as col2_1
        ,       regexp_substr(col2, '\w+', 1, 2) as col2_2
        ,       regexp_substr(col2, '\w+', 1, 3) as col2_3
        ,       regexp_substr(col2, '\w+', 1, 4) as col2_4
        --      if there is more add more...
        from    src
)
select col1, col2_1 from explode where col2_1 is not null union all
select col1, col2_2 from explode where col2_2 is not null union all
select col1, col2_3 from explode where col2_3 is not null union all
select col1, col2_4 from explode where col2_4 is not null 
order by col1
;

[nenu_devudni]

TRY THIS .. and add the rest of months from june to dec ... 

[prabhu_pramod]

Try this

select mydate, t1.* from mytable t1,

(select add_months(to_date('01/01/2017','MM/DD/YYYY'), -1+rownum) mydate from dual CONNECT BY LEVEL <= 12) t2

where t2.mydate = t1.realdate(+)

;

 

Change the 01/01/2017 to desired date and LEVEL value of 12 to get more or less rows