一维表格转二维表格的问题，请指点

[shumark]

Tbale1
----------
PId name sex
1 aaa 1
2 bbb 0
3 ccc 1
4 ddd 0
5 eee 1

Table2
---------
CId Pid Num Year
1 2 200 2000
2 2 240 2001
3 2 20 2002
4 2 230 2003
5 3 100 2000
6 3 140 2001
7 3 10 2002

---------需要的结果是：
year bbb ccc
2000 200 100
2001 240 140
2002 20 10
2003 230 0(没有数据，可以空或者是0）

请高手指点！

[canyqf]

select Table2.year,
 sum(case when Table1.name = 'bbb' then Table1.Num else 0 end) As bbb,
 sum(case when Table1.name = 'ccc' then Table1.Num else 0 end) As ccc
from Table2
 inner join Table1 on Table1.PID = Table2.PID
group by Table2.year

[shumark]

本人sql水平不高，请高人指点。。

[canyqf]

还指点什么？查一下就是结果...

[canyqf]

哦，不好意思，上面写错了一些东西，还忽略了关键字。
修改一下，这个查询得到就是结果：

select Table2.[year],
 sum(case when Tbale1.[name] = 'bbb' then Table2.Num else 0 end) As bbb,
 sum(case when Tbale1.[name] = 'ccc' then Table2.Num else 0 end) As ccc
from Table2
 inner join Tbale1 on Tbale1.PID = Table2.PID
group by Table2.[year]

[mengxj85]

Up

[skeljy]

mark

[shumark]

create table Table1 (PId int,name varchar(10),sex int)
insert into Table1 values(1 ,'aaa' ,1)
insert into Table1 values(2 ,'bbb' ,0)
insert into Table1 values(3 ,'ccc' ,1)
insert into Table1 values(4 ,'ddd' ,0)
insert into Table1 values(5 ,'eee' ,1)
create table Table2(CId int, Pid int, Num int, [Year] int)
insert into table2 values(1 , 2 , 200, 2000)
insert into table2 values(2 , 2 , 240, 2001)
insert into table2 values(3 , 2 , 20 , 2002)
insert into table2 values(4 , 2 , 230, 2003)
insert into table2 values(5 , 3 , 100, 2000)
insert into table2 values(6 , 3 , 140, 2001)
insert into table2 values(7 , 3 , 10 , 2002)
go


select Table2.[year],
sum(case when table1.[name]= 'bbb' then Table2.Num else 0 end) As bbb,
sum(case when table1.[name] = 'ccc' then Table2.Num else 0 end) As ccc
from Table2
inner join table1 on table1.PID = Table2.PID
group by Table2.[year]

[Mcfly_SJ]

受教了，多谢