Generate dynamic table of all months between two dates

Question

I have a table that looks like this:

Name	INT_VALUE	Start	END
a	100	2013-11-16	2014-11-16

The goal is to output the following:

Name	INT_VALUE	Start	END	interval
a	100	2013-11-16	2014-11-16	11-2013
a	100	2013-11-16	2014-11-16	December 2013
a	100	2013-11-16	2014-11-16	1-2014
a	100	2013-11-16	2014-11-16	February 2014

I've started using this. But I'm not sure how to use the START and END dates in the table to determine the start and end of the list of months between calculations.

SELECT START, calendar, COUNT(1)
FROM table1 AS t1
    RIGHT JOIN (SELECT row_number() OVER (ORDER BY SEQ4()) AS MONTHS
                , TO_DATE(DATEADD(MONTH, MONTHS, '2019-05-01')) AS calendar
                FROM TABLE(GENERATOR(rowcount=>80)))
            ON t1.START = calendar
GROUP BY 1, 2
ORDER BY 2, 1
;

The goal here is to associate an int value with each month between the start and end of the range.

Answer 1

Moved the number range into its own CTE to separate things, we now have a large list of 80 numbers (probably larger).

Then we find the number of months between start/end and concatenate the same number of rows. Then do the math to convert the range into a selection:

WITH range_of_numbers AS (
    SELECT 
        row_number() OVER (ORDER BY SEQ4())-1 AS rn
    FROM TABLE(GENERATOR(rowcount=>80))
)
SELECT 
    t1.name, 
    t1.int_value, 
    t1.start,
    t1.end,
    DATEADD(MONTH, r.rn, t1.start) as interval
FROM table1 AS t1
JOIN range_of_numbers as r
    ON date_diff('month', t1.START, t1.end) <= between r.rn
ORDER BY 2,1,3;

Another option is to build a long range date table

CREATE TABLE dates AS 
SELECT 
     DATEADD(MONTH, row_number() OVER (ORDER BY SEQ4())-1, '1980-01-01') as month_date
FROM TABLE(GENERATOR(rowcount=>8000))

Then we use BETWEEN to get the included values ​​in the (start, end) range, which becomes:

FROM table1 AS t1
JOIN dates as d
    ON d.month_date BETWEEN t1.START AND t1.end

Answer 2

WITH RECURSIVE
cte AS ( SELECT name, int_value, start, `end`, 
                1 rownum, DATE_FORMAT(start, '%m-%Y') `interval`
         FROM source_table
         UNION ALL
         SELECT name, int_value, start, `end`, 
                1 + rownum, DATE_FORMAT(start + INTERVAL rownum MONTH, '%m-%Y')
         FROM cte
         WHERE start + INTERVAL rownum - 1 MONTH < `end` )
         
SELECT name, int_value, start, `end`, `interval`
FROM cte
ORDER BY rownum;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bdd028a7755fdcb8296df2301baeb295

If you don't want the month to have leading zeros, use the '%c-%Y' pattern.