Generate 1 row for every month between a range of dates

Question

I have the following data, as an example:

Name  | Value       | startdate   | enddate
1     | 10          | 2023-04-01  | 2023-06-30
2     | 99          | 2023-03-01  | 2023-05-01

I need to get the following output:

Name  | Value       | date
1     | 10          | 2023-04
1     | 10          | 2023-05
1     | 10          | 2023-06
2     | 99          | 2023-03
2     | 99          | 2023-04
2     | 99          | 2023-05

Which, in resume, is a new row with the same Value for every month between the date range for every Name column.

Tried some solutions seen here and here, but didn't turned out so great, since it's not the same expected result, and sometimes having the startDate on 2022 and EndDate on 2023 for example breaks some mentioned solutions.

What I made so far:

SELECT
    value,
    DATE_FORMAT(startdate, "%Y-%m") AS startMonth,
    DATE_FORMAT(enddate, "%Y-%m") AS endMonth

FROM
    table

GROUP BY
    --Here's my stuggle

A procedure would help too! As long as it generates that output.

Answer 1

with recursive dates(name, date) as (
  select name, startdate from mytable where name = 1
  union all
  select dates.name, dates.date + interval 1 month from dates join mytable using (name)
  where dates.date < mytable.enddate
)
select * from dates;

Output:

+------+------------+
| name | date       |
+------+------------+
|    1 | 2023-04-01 |
|    1 | 2023-05-01 |
|    1 | 2023-06-01 |
|    1 | 2023-07-01 |
+------+------------+

Dbfiddle

This is off by one, I'll leave it to you to correct the terminating condition. But it demonstrates the technique of creating a series of successive values using a common table expression.

This is also in the code examples from MySQL: https://dev.mysql.com/blog-archive/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes-part-two-how-to-generate-series/