合并数据的问题

[xhunanpp]

怎么把t1 表数据 变成 t2```

t1
-------------------------------
F1 F2 F3 F4
-------------------------------
A 2 B 2
A 8 C 8
B 10 C 10
C 21 C 21
D 4 D 4
-------------------------------

t2:
-------------------------------
F1 F2 F4
-------------------------------
A 11 0
B 10 2
C 21 39
D 4 4

[zheninchangjiang]

select isnull(a.f1,b.f3) as f1,a.f2,b.f4 from
(select f1,sum(f2) as f2 from t1 group by f1) a
full join
(select f3,sum(f4) as f4 from t1 group by f3) b
on a.f1=b.f3

[zjcxc]

select F1=isnull(a.F1,b.F3),F2=isnull(a.F2,0),F4=isnull(b.F4,0)
from(
select F1,F2=sum(F2)
from t1
group by F1
)a full join(
select F3,F4=sum(F4)
from t1
group by F3
)b on a.F1=b.F3