In a list of Product and Year, use a Recursive Function to Include products of previous years

Question

DB-Fiddle

This is a simplification of the problem I'm facing. It boils down to a CTE that shows a list of products of the current and previous years. For the first year (2018), it will only show 2018 products. For the second year (2019), it will show 2019 + 2018 products, etc.

I don't know if a CTE is the best solution, but needed to start somewhere.

Expected Result:

year	product
2018	product2018A
2018	product2018B
2019	product2018A
2019	product2018B
2019	product2019
2020	product2018A
2020	product2018B
2020	product2019
2020	product2020A
2020	product2020B

Attempt:

DECLARE   @YearProduct  TABLE(
    [year] int,
    product VARCHAR(20)
);

INSERT INTO @YearProduct
([year], product)
VALUES 
('2018', 'product2018A'),
('2018', 'product2018B'),
('2019', 'product2019'),
('2020', 'product2020A'),
('2020', 'product2020B')

;WITH  CTE AS(  
SELECT  [year], product
FROM @YearProduct
UNION ALL
SELECT  yp.[year], yp.product
FROM @YearProduct yp
  INNER JOIN CTE ON CTE.[year]+1 = (yp.[year] ) AND CTE.product = yp.product
)
SELECT * FROM CTE

Answer 1

Recursive cte is the right way. What you want is CROSS JOIN with a year table for those product with LESS OR EQUAL TO year

SELECT Y.[year], P.product
FROM   @YearProduct P
       INNER JOIN 
       (
           SELECT distinct [year] 
           FROM   @YearProduct
       ) Y    ON P.[year] <= Y.[year]
ORDER BY Y.[year]

dbfiddle