查询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
------解决方案--------------------
--> 测试数据：[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 行受影响)