Average grade of student temporary table in SQL Server

Question

I have a database with the following tables:

• Students_T (SNo, SDedc, SAddress)
• Courses_T (CNo, CDesc)
• CoursesRegister_T (CRNo, CR_CNo, CR_SNo, CRGrade)

I need to represent the following data:

For each student show:

Student desc, Course desc, Course grade, Grades average

And I need to do this for every course that the student register in.

For example:

if student A is registered to course B and his grade is 90 and he's also registered to course C and his grade is 70, I suppose to get the following table:

A B 90 80
A C 70 80

The important thing is that I need to use temporary tables with SELECT INTO syntax and I can't figure it out.

I'm using SQL Server.

Someone know how to do that?

EDIT:

I already did this:

select CR_ST_No, ST_Desc, CR_Desc, CR_Grade
into #Grades
from Students_T
left join CoursesRegister_T on CR_ST_NO = ST_No
where CRS_Desc is not null

select *
from #Grades 
drop table #Grades

and it's giving me table with all data I need except the average.

When I try to change the row select * to select *, avg(CR_Grade) it can't execute.

Answer 1

This is a typical use case for a Window Function. You haven't provided any sample data and I can't test it right now, but something like that should work:

SELECT s.SDedc, c.CDesc, r.CRGrade, AVG(r.CRGrade) OVER (PARTITION BY s.SNo) AS average_grade 
    FROM Students_T             AS s 
    LEFT JOIN CoursesRegister_T AS r ON r.CR_SNo = s.SNo 
    LEFT JOIN Courses_T         AS c ON r.CR_CNo = c.CNo;