SQL Generate per day records between dates-CodePudding

I have a table that where each record has start and end dates. I need to return a record for each day between start and end date fields (including the start and end dates). Using MS SQL Server.

Example:

Current data

Data required:

Looking for recommendations. Thanks.

CodePudding user response：

You can use recursive cte :

with cte as (
     select id, startdate, enddate, startdate as date
     from table t
     union all
     select id, startdate, enddate, dateadd(day, 1, date)
     from cte c
     where date < enddate
)
select *
from cte c
option (maxrecursion 0);