Search code examples
sqlsql-serverjoinleft-join

Join two tables statement

I have two tables as shown below:

Table1:

Entity Value
A 5
B 10

Table2:

Date
1/1/2023
2/1/2023
3/1/2023
4/1/2023
5/1/2023

The final result expected:

Entity Value Date
A 5 1/1/2023
A 6 2/1/2023
A 7 3/1/2023
A 8 4/1/2023
A 9 5/1/2023
B 10 1/1/2023
B 11 2/1/2023
B 12 3/1/2023
B 13 4/1/2023
B 14 5/1/2023

A usual left join can join the date column from table2 to table1 but how do I increment the value column based on 'row numbers' from the date column?

Solution

  • A cross join will give you the cartesian product of your two tables (ie match each row in t1 to every row in t2), row_number will provide your new values:

    select t1.Entity, t2.Date, 
  t1.Value - 1 + Row_Number() over(partition by entity order by date) as Value
from t1
cross join t2
order by Value;