how extracts for every years a row

Question

I have My db one row that I have obtain with a query:

DATE_START     DATE_END
31/12/2014     07/07/2018

I need to extract every years between date_start and date_end My purpose is obtain this:

I do this

SELECT LISTAGG(extract(year from TO_DATE(DATE_START,'dd-MM-yyyy')) ) WITHIN GROUP ( order by oo.DATE_START )
                        
                    from  STUDENT oo
                    WHERE 
                    ...

the problem is the query return me only a value and not the five values that I want. Anyone can help me?

`LISTAGG` is an aggregation function, as it name implies. Aggregate frunctions are used to reduce the dataset. — astentx, Jul 27 '21 at 10:19
Does this answer your question? [Generating dates between two dates](https://stackoverflow.com/questions/16207543/generating-dates-between-two-dates) or [Get list of numbers in between two columns with key](https://stackoverflow.com/questions/7467040/get-list-of-numbers-in-between-two-columns-with-key) — astentx, Jul 27 '21 at 10:20
I'd suggest to read this: [Recursive Common Table Expressions](https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html#common-table-expressions-recursive) — Maciej Los, Jul 27 '21 at 10:24

TimLer · Answer 1 · 2021-07-27T10:38:20.793

When sub-query needs to be executed multiple times at that time, You can use With clause

with myTab(myYear) as
(
   select case minStart < minEnd then minStart else minEnd as minYear,
          case maxStart > maxEnd then maxStart else maxEnd as maxYear,
   from 
     (
        select min(extract(year from date_start) as minStart, max(extract(year from date_start)) as maxStart,
               min(extract(year from date_end) as minEnd, max(extract(year from date_end)) as maxEnd from table
     ) t
    
     union all
   
    select minYear + 1, maxYear
    from myTab
    where minyear < maxYear
)
select minYear
from myTab
order by minYear;

how extracts for every years a row

1 Answers1