Код

create table #Tree
(
  id	  char(9) not null,
  parentid  char(9) null,
  descr     varchar(100) null,
  isfolder  numeric(1, 0) not null,
  lvl	 int not null,
  sort	varbinary(200) null,
  ident     int identity
)

declare @lvl   int,
	  @void  char(9)

set @void = '     0   '
set @lvl = 1

-- создать корень
insert into #Tree (id, parentid, descr, isfolder, lvl, sort)
  select @void, @void, NULL, 0 , 0, NULL

-- заполнить элементами первого уровня
insert into #Tree (id, parentid, descr, isfolder, lvl, sort)
  select id, parentid, descr, isfolder, @lvl, cast(0 as binary(4))
    from sc62 (nolock)
    where (parentid = @void)
    order by isfolder, cast(descr as varbinary(100))

while (@@rowcount > 0) begin
  set @lvl = @lvl + 1
  insert into #Tree (id, parentid, descr, isfolder, lvl, sort)
    select t.id, t.parentid, t.descr, t.isfolder, @lvl, tt.sort + cast(tt.ident as binary(4))
	from sc62 t (nolock)
	join #Tree tt
	  on (tt.id = t.parentid) and (tt.lvl = @lvl - 1)
	order by t.isfolder, cast(t.descr as varbinary(100))
end

update #Tree set sort = sort + cast(ident as binary(4)) 

Код

select
    t.id id, o.qty as qty,
    t.isfolder, t.lvl, t.parentid, t.sort
  into #Main
  from #Tree t
  left join #Remains o
    on (o.id = t.id) 

Код

declare @maxlevel int
select @maxlevel = max(lvl) from #Main
while (@maxlevel >= 0) begin
  set @maxlevel = @maxlevel - 1
  update #Main
    set
    qty = (
	   select isnull(sum(mm.qty),0)
	     from #Main mm
	  where (mm.parentid = #Main.id)
    )
  where (#Main.lvl = @maxlevel) and (#Main.isfolder < 2)
end 

Код

if not (select OBJECT_ID('products')) is NULL
	drop table products

create table products (
  id int,
  parentid int,
  descr char(30),
  isfolder int
)

insert products (id, parentid, descr, isfolder)
	select 1, NULL, 'Вода', 1
	union all
	select 2, NULL, 'Земля', 1
	union all
	select 3, NULL, 'Прочее', 1
	union all
	select 4, 3, 'Огонь', 1
	union all
	select 101, 1, 'Вода лёгкая', 2
	union all
	select 102, 1, 'Вода тяжёлая', 2
	union all
	select 103, 2, 'Земля лёгкая', 2
	union all
	select 104, 2, 'Земля тяжёлая', 2
	union all
	select 105, 4, 'Огонь прочий', 2


if not (select OBJECT_ID('remains')) is NULL
	drop table remains

create table remains (
  id int,
  qty int
)

insert remains (id, qty)
	select 101, 10
	union all
	select 102, 5
	union all
	select 104, 100
	union all
	select 105, -2 

Код

select
  id, sum(qty) qty
from remains
group by id
	with rollup