trying to get more rows from the table than there actually exist

Question

I was not sure about a descriptive Title of this question so I put there what i thought to be the best sentence.

I have a table like this:

StudentID, Month, Year, Present
-------------------------------
1, 1, 2016, P
1, 12, 2016, P
1, 4, 2017, P

I want to write SQL statement, without using cursor, to return a resultset like the below one:

Required output:

1, 1, 2016, P -- <-- it is 'P' in the table
1, 2, 2016, A
1, 3, 2016, A
1, 4, 2016, A
1, 5, 2016, A
1, 6, 2016, A
1, 7, 2016, A
1, 8, 2016, A
1, 9, 2016, A
1, 10, 2016, A
1, 11, 2016, A
1, 12, 2016, P -- <-- it is 'P' in the table
1, 1, 2017, A
1, 2, 2017, A
1, 3, 2017, A
1, 4, 2017, P -- <-- it is 'P' in the table
1, 5, 2017, A
1, 6, 2017, A
1, 7, 2017, A
1, 8, 2017, A
1, 9, 2017, A
1, 10, 2017, A
1, 11, 2017, A
1, 12, 2017, A

is this possible through one SQL statement, without using cursors.

I am working on SQL Server 2000 database.

Answer 1

    declare @t table(StudentID int, Month int, Year int, Present char(1));
    insert into @t 
    select 1, 1, 2016, 'P' union all
    select 1, 12, 2016, 'P' union all
    select 1, 4, 2017, 'P';

    select y.StudentID,
           y.y, m.m, 
           isnull(t.Present, 'A') as P 
    from
    (
    select StudentID, year as y       
    from @t
    group by StudentID, year
    ) y
    cross join

    (
    select number as m 
    from master..spt_values
    where type = 'p' and number between 1 and 12
    )m
    left join @t t
       on t.StudentID = y.StudentID and t.year = y.y  and t.month = m.m;

Explanation: we need as much rows as the number of students per number of years per 12 month, so I use cross join of distinct pairs student, year and 12 months.

As we have SQL Server 2000, we cannot use Row_number() to generate 12 values, niether VALUES, so we can join to a number table (I used spt_values even if I personally have a number table until 10000000) or we could generate in other ways, for example like this :

select 1 as m union all select 2...union all select 12

And finally join the presence list to a generated 'calendar' for every student