I have an SQLite 3.38.2 table that has rows for certain years:

with data (year_, amount) as (
values 
(2024, 100),
(2025, 200),
(2025, 300),
(2026, 400),
(2027, 500),
(2028, 600),
(2028, 700),
(2028, 800),
(2029, 900),
(2031, 100)
)
select * from data

Here's the Demo.

I want at least one row for each year within this range: system year 9. In other words, I want rows for 10 years, starting with the current year (currently 2023).

However, I'm missing rows for certain years: 2023, 2030, and 2032. So I want to generate filler rows for those missing years. The amount for the filler rows would be null.

It would look like this:

In an SQLite query, how can I select the rows and generate filler rows within the 10 year range?

Edit: I would prefer not to manually create a list of years in the query or in a table. I would rather create a dynamic range within the query.

CodePudding user response：

You may use a calendar table approach, which will require keeping a table containing all years which you want to appear in your report. Left join this calendar table to your current table to get the result you want.

WITH years AS (
    SELECT 2023 AS YEAR_ UNION ALL
    SELECT 2024 UNION ALL
    ...
    SELECT 2040
)

SELECT y.YEAR_, d.AMOUNT
FROM years y
LEFT JOIN data d
    ON d.YEAR_ = y.YEAR_
WHERE d.YEAR_ BETWEEN SYSYEAR AND SYSYEAR   9
ORDER BY d.YEAR_;

Note that in practice, rather than using the above CTE years, you may create a bona fide table containing the next hundred years in it.

CodePudding user response：

Yet another option is using a recursive approach to generate your N dates:

WITH RECURSIVE cte AS (
     SELECT 2023 AS year_
  
     UNION ALL
   
     SELECT year_   1
     FROM cte
     WHERE year_ < 2023   (10) -1
)
SELECT cte.year_, data.amount
FROM      cte
LEFT JOIN data
       ON cte.year_ = data.year_

Check the demo here.