How to create row data for a date range with SQL

The table looks like this:

---+------------+------------+---------
id | start_date | end_date   | amount |
---+------------+------------+---------
 1 | 2021-01-01 | 2021-01-07 | 100    |
---+------------+------------+---------

I want to change the table above to the following.

---+------------+------------+--------+---------------+
id | start_date | end_date   | amount | operation_date|
---+------------+------------+--------+---------------+
 1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-01    |
 1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-02    |
 1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-03    |
 1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-04    |
 1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-05    |
 1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-06    |
 1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-07    |
---+------------+------------+--------+---------------+

I want to make the operation date a row according to the range of start date and end date and input the same information.

Can you help? I’ve done a lot of searching, but I can’t find a suitable answer.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I think you can use RECURSIVE CTE like:

WITH RECURSIVE cte AS (
SELECT id, start_date, end_date, amount, start_date AS operation_date 
FROM table1 
 UNION ALL
SELECT id, start_date, end_date, amount, operation_date+INTERVAL 1 DAY 
FROM cte 
 WHERE operation_date+INTERVAL 1 DAY <= end_date)
  SELECT * 
    FROM cte ;

id	start_date	end_date	amount	operation_date
1	2021-01-01	2021-01-07	100	2021-01-01
1	2021-01-01	2021-01-07	100	2021-01-02
1	2021-01-01	2021-01-07	100	2021-01-03
1	2021-01-01	2021-01-07	100	2021-01-04
1	2021-01-01	2021-01-07	100	2021-01-05
1	2021-01-01	2021-01-07	100	2021-01-06
1	2021-01-01	2021-01-07	100	2021-01-07

Here’s a demo fiddle

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0