求一条sql查询语句（急！！！！）

[chenrisheng91]

查询A表得出B表
A表 B表
id name state name state1 state2 state3 state4
1 a 1 a 1 1 0 0
2 c 2 b 1 0 0 1
3 d 3 c 0 1 1 0
4 b 4 d 0 0 1 0
5 a 2
6 b 1
7 c 3

[Yole]

--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([ID] int,[name] varchar(6),[state] int)
insert [a]
select 1,'a',1 union all
select 2,'c',2 union all
select 3,'d',3 union all
select 4,'b',4 union all
select 5,'a',2 union all
select 6,'b',1 union all
select 7,'c',3  

--select * from a 

select [name],
sum(case when state=1 then 1 else 0 end) as [state1],
sum(case when state=2 then 1 else 0 end) as [state2],
sum(case when state=3 then 1 else 0 end) as [state3],
sum(case when state=4 then 1 else 0 end) as [state4]
from a 
group by [name]

name state1 state2 state3 state4 ------ ----------- ----------- ----------- ----------- a 1 1 0 0 b 1 0 0 1 c 0 1 1 0 d 0 0 1 0 (4 行受影响)