--> 测试数据：[A1]
go
if object_id('[A1]') is not null 
drop table [A1]
go
create table [A1](
[id] int,
[num] int,
[class] varchar(5)
)
go
insert [A1]
select 1,10,'类别1' union all
select 1,101,'类别2' union all
select 2,20,'类别1' union all
select 2,50,'类别1'

--> 测试数据：[B1]
go
if object_id('[B1]') is not null 
drop table [B1]
create table [B1](
[id] int,
[num] int,
[class] varchar(5)
)
go
insert [B1]
select 1,100,'类别1' union all
select 1,1001,'类别2' union all
select 1,1000,'类别3' union all
select 2,20,'类别1' union all
select 2,20,'类别2'

go
create table #t(
id int,
numa int,
numb int,
class varchar(5)
)
insert #t
select b.id,sum(isnull(a.num,0)) as numa,sum(isnull(b.num,0)) as numb,b.class
from [B1] b left join [A1] a
on b.id=a.id group by  b.id,b.class
select * from #t

declare @str varchar(1000)
set @str=''
select @str=@str+','+class+'=sum(case when class='
     +QUOTENAME(class,'''')+'then numa+numb else 0 end)'
from #t group by class
set @str='select id'+@str+' from #t group by id'
exec(@str)
----------------------------------
id    类别1    类别2    类别3
1    311    2113    2111
2    110    110    0