SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sample_table 
    (
        name VARCHAR(50),
        age INTEGER,
        year INTEGER,
        color VARCHAR(50),
        food VARCHAR(50),
        sport VARCHAR(50)
    );
    
    INSERT INTO
      sample_table (name, age, year, color, food, sport)
    VALUES
      ('aaa', 41,  2010, 'Red', 'Pizza', 'hockey'),
      ('aaa', 42,  2012, 'Red', 'Pizza', 'hockey'), 
      ('aaa', 47, 2017, 'Red', 'Pizza', 'hockey'), 
      ('bbb', 20, 2000, 'Blue', 'Burgers','football'),
      ('bbb', 26,  2006, 'Blue', 'Burgers', 'football'),
      ('bbb', 30, 2010, 'Blue', 'Burgers', 'football');

SELECT * FROM sample_table


name
age
year
color
food
sport


aaa
41
2010
Red
Pizza
hockey

aaa
42
2012
Red
Pizza
hockey

aaa
47
2017
Red
Pizza
hockey

bbb
20
2000
Blue
Burgers
football

bbb
26
2006
Blue
Burgers
football

bbb
30
2010
Blue
Burgers
football


    create table years_table (year integer);
    
    insert into years_table(year)
    values(2000),(2001),(2002),(2003),(2004),(2005),(2006),(2007),(2008),(2009),(2010);
    insert into years_table(year)
    values(2011);
    insert into years_table(year)
    values(2012);
    insert into years_table(year)
    values(2013);
    insert into years_table(year)
    values(2014);
    insert into years_table(year)
    values(2015);
    insert into years_table(year)
    values(2016);
    insert into years_table(year)
    values(2017);
    insert into years_table(year)
    values(2018);
    insert into years_table(year)
    values(2019);
    insert into years_table(year)
    values(2020);

SELECT * FROM years_table


year


2000

2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

2013

2014

2015

2016

2017

2018

2019

2020


WITH
  person
AS
(
  SELECT
    name,
    MIN(year)   AS min_year,
    MAX(year)   AS max_year
  FROM
    sample_table
  GROUP BY
    name
)
SELECT
  p.name,
  y.year,
  s.color,
  s.food,
  s.sport,
  y.year - (s.year - s.age) AS age
FROM
  person        AS p
INNER JOIN
  years_table   AS y
    ON y.year BETWEEN p.min_year and p.max_year
CROSS APPLY
(
  SELECT TOP 1 *
    FROM sample_table
   WHERE name  = p.name
     AND year <= y.year
ORDER BY year DESC
)
  AS s


name
year
color
food
sport
age


aaa
2010
Red
Pizza
hockey
41

aaa
2011
Red
Pizza
hockey
42

aaa
2012
Red
Pizza
hockey
42

aaa
2013
Red
Pizza
hockey
43

aaa
2014
Red
Pizza
hockey
44

aaa
2015
Red
Pizza
hockey
45

aaa
2016
Red
Pizza
hockey
46

aaa
2017
Red
Pizza
hockey
47

bbb
2000
Blue
Burgers
football
20

bbb
2001
Blue
Burgers
football
21

bbb
2002
Blue
Burgers
football
22

bbb
2003
Blue
Burgers
football
23

bbb
2004
Blue
Burgers
football
24

bbb
2005
Blue
Burgers
football
25

bbb
2006
Blue
Burgers
football
26

bbb
2007
Blue
Burgers
football
27

bbb
2008
Blue
Burgers
football
28

bbb
2009
Blue
Burgers
football
29

bbb
2010
Blue
Burgers
football
30
name	age	year	color	food	sport
aaa	41	2010	Red	Pizza	hockey
aaa	42	2012	Red	Pizza	hockey
aaa	47	2017	Red	Pizza	hockey
bbb	20	2000	Blue	Burgers	football
bbb	26	2006	Blue	Burgers	football
bbb	30	2010	Blue	Burgers	football