Recursive average for hierarchical organization

Question

I need to calculate averages for a hierarchical organization. Each student can have grades in different subjects (not all students have grades in all subjects), and each student has a Parent (which is a unit). Each unit has a parent which is another unit, and so on. The number of branches in the hierarchy tree is unknown (that is why I think I need a recursive approach). Also, the number of subjects is unknown so I can't use any pivot-like technique to my understanding.
My final goal is to visualize the hierarchy using a Matrix in Report Builder and the Recursive Parent feature to create the hierarchy.

For each unit that holds students I need to calculate the average for each subject. For each unit that holds units, I need to calculate the average of its sub-units for each subject (meaning average of averages).

I'm trying to go for an iteration based approach as advised by @JamesZ:

insert into temp
select * from UnitsAvg

declare @level int = (select max(level) from hierarchy)

while (@level >= 0)
begin
insert into temp
select h.unitparentid as id, u.subject, avg(AvgGrade) as AvgGrade
from UnitsAvg u
inner join hierarchy h
on u.unitid=h.unitid
where level=@level
group by h.unitparentid, u.subject
@level = @level - 1
end

select * from temp

Something is off with the syntax there, help would be appreciated! the fiddle

Answer 1

This is an iteration based approach to the issue, maybe this helps or someone else can figure out how to do the updates without a loop. The first part of the CTE is to figure out what subjects exist in the hierarchy. It might not be the optimal one but it was copied from the answer to the event question, so also the alias names are little bit strange :)

;with CTE as (
  select S.StudentId as UnitID, S.ParentID as UnitParentID,
    S.StudentID, Subject, 'S' as Type
  from grades S
union all
  select U.UnitId, U.UnitParentId, 
    CTE.StudentId as StudentID, Subject, 'U' as Type
  from
    Hierarchy U
    join CTE
      on U.UnitId = CTE.UnitParentId
)
select distinct UnitID, UnitParentId, Subject, -1 as Grade
into #tmp
from CTE
where Type = 'U'

while (1=1) begin
  update #tmp 
  set Grade = (select avg(Grade)
    from (
      select Grade from grades g 
      where g.ParentId = #tmp.UnitID and 
      g.Subject = #tmp.Subject

      union all

      select Grade from #tmp t 
      where t.UnitParentID = #tmp.UnitID and 
      t.Subject = #tmp.Subject
    ) X
  )
  where 
    Grade = -1 and
    not exists (
      select 1 from #tmp t
      where t.UnitParentID = #tmp.UnitID and t.Grade = -1
    )

  if (@@rowcount = 0) break
end

The loop updates values those units that can be updated (=the hierarchy beneath them has been calculated already). The grade -1 is used to figure out if the value has been calculated or not. The loop ends if there was no rows to update. If there are subunits and students in the unit, grades from both are calculated with the same weight.

SQL Fiddle