Search code examples
sqlt-sqlselectwhile-loopcursor

T-SQL - adding more date values to a table all rows

I have a table that contains many IDs as random numbers and I would like to create another table that contains all the IDs with another column having dates from first day of the year until a specific date.

For example: My actual table looks like:

ID  
101  
431  
566

And I would like to have a table if my specific end date is 2020-01-03 that looks like:

ID Date  
101 2020-01-01  
101 2020-01-02  
101 2020-01-03  
431 2020-01-01  
431 2020-01-02  
431 2020-01-03  
566 2020-01-01  
566 2020-01-02  
566 2020-01-03

Could you help me to solve my issue? Thanks in advance!

Solution

  • You can use a recursive CTE to define the dates and then cross join:

    with dates as (
      select convert(date, '2020-01-01') as dte
      union all
      select dateadd(day, 1, dte)
      from dates
      where dte < @enddate
     )
select t.id, dates.date
from t cross join
     dates
option (maxrecursion 0);