MySQL - How can I get a row multiple times based on a value of the row?

Question

I have a table of events in this format:

ID | NAME | DATEFROM | DATEUNTIL
1 Event1 2019-12-01 2019-12-03
2 Event2 2019-12-07 2019-12-08
3 Event3 2019-12-11 2019-12-12

I need to retrieve all the events, but instead of DATEFROM and DATEUNTIL, it should be something like this:

ID | NAME | DATE
1 Event1 2019-12-01
1 Event1 2019-12-02
1 Event1 2019-12-03
2 Event2 2019-12-07
2 Event2 2019-12-08
3 Event3 2019-12-11
3 Event3 2019-12-12

What query should I use?

Many thanks in advance.

Typically, either a table of "all" dates would be joined to; in more recent versions of MySQL a CTE can be used to generate something akin to such a date table. Which version of MySQL are you using? — Uueerdo, Dec 20 '19 at 22:36
Check here : https://www.w3schools.com/php/php_mysql_select.asp or check crud system — , Dec 20 '19 at 22:36

score 1 · Accepted Answer · answered Dec 20 '19 at 22:39

If you are running MySQL 8.0, you can do this with a recursive cte:

with recursive cte as (
    -- this is your anchor
    select id, name, datefrom, dateuntil from mytable
    union all
    -- expand the dataset by incrementing "datefrom" until it reaches "dateuntil"
    select id, name, datefrom + interval 1 day, dateuntil
    from cte
    where datefrom < dateuntil
)
select id, name, datefrom date from cte order by id, date

Demo on DB Fiddle:

id | name   | date      
-: | :----- | :---------
 1 | Event1 | 2019-12-01
 1 | Event1 | 2019-12-02
 1 | Event1 | 2019-12-03
 2 | Event2 | 2019-12-07
 2 | Event2 | 2019-12-08
 3 | Event3 | 2019-12-11
 3 | Event3 | 2019-12-12

MySQL - How can I get a row multiple times based on a value of the row?

1 Answers1