Postgres 15

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');
    
    INSERT INTO sample_table (name, age, year, color, food,sport)
    VALUES ('aaa', 42,  2012, 'Red', 'Pizza', 'hockey');

    INSERT INTO sample_table (name, age, year, color, food,sport)
    VALUES ('aaa', 47, 2017, 'Red', 'Pizza', 'hockey');
    
    INSERT INTO sample_table (name, age, year, color, food,sport)
    VALUES ('bbb', 20, 2000, 'Blue', 'Burgers','football');
    
    INSERT INTO sample_table (name, age,  year, color, food,sport)
    VALUES ('bbb', 26,  2006, 'Blue', 'Burgers', 'football');


    INSERT INTO sample_table (name, age, year, color, food,sport)
    VALUES ('bbb', 30, 2010, 'Blue', 'Burgers', 'football');

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


  create table years_table (year integer);
    

with recursive yearlist(year) as 
(
    select 2000 as year
    union all
    select yl.year + 1 as year
    from yearlist yl
    where yl.year + 1 <= date_part('year',CURRENT_DATE)
)
    insert into years_table(year)
select year from yearlist order by year desc;;
    
 

CREATE TABLE



INSERT 0 24


    
    select name, year
      , max(color) over(partition by name, grp order by year) color
      , max(sport) over(partition by name, grp order by year) sport
      , max(food) over(partition by name, grp order by year) food
    from ( 
        select n.name, y.year, t.color, t.food, t.sport,
            sum(case when t.name is null then 0 else 1 end) over(partition by n.name order by y.year) grp
        from (
            select name, min(year) min_year, max(year) max_year
            from sample_table
            group by name
        ) n
        inner join years_table y on y.year between n.min_year and n.max_year
        left join sample_table t on t.name = n.name and t.year = y.year
    ) t


name
year
color
sport
food


aaa
2010
Red
hockey
Pizza

aaa
2011
Red
hockey
Pizza

aaa
2012
Red
hockey
Pizza

aaa
2013
Red
hockey
Pizza

aaa
2014
Red
hockey
Pizza

aaa
2015
Red
hockey
Pizza

aaa
2016
Red
hockey
Pizza

aaa
2017
Red
hockey
Pizza

bbb
2000
Blue
football
Burgers

bbb
2001
Blue
football
Burgers

bbb
2002
Blue
football
Burgers

bbb
2003
Blue
football
Burgers

bbb
2004
Blue
football
Burgers

bbb
2005
Blue
football
Burgers

bbb
2006
Blue
football
Burgers

bbb
2007
Blue
football
Burgers

bbb
2008
Blue
football
Burgers

bbb
2009
Blue
football
Burgers

bbb
2010
Blue
football
Burgers



SELECT 19

name	year	color	sport	food
aaa	2010	Red	hockey	Pizza
aaa	2011	Red	hockey	Pizza
aaa	2012	Red	hockey	Pizza
aaa	2013	Red	hockey	Pizza
aaa	2014	Red	hockey	Pizza
aaa	2015	Red	hockey	Pizza
aaa	2016	Red	hockey	Pizza
aaa	2017	Red	hockey	Pizza
bbb	2000	Blue	football	Burgers
bbb	2001	Blue	football	Burgers
bbb	2002	Blue	football	Burgers
bbb	2003	Blue	football	Burgers
bbb	2004	Blue	football	Burgers
bbb	2005	Blue	football	Burgers
bbb	2006	Blue	football	Burgers
bbb	2007	Blue	football	Burgers
bbb	2008	Blue	football	Burgers
bbb	2009	Blue	football	Burgers
bbb	2010	Blue	football	Burgers