select no ,Max(case when status=1 then status else 0 end) as status_1,
Max(case when status=2 then status else 0 end) as status_2,
Max(case when status=3 then status else 0 end) as status_3,
Max(case when status=4 then status else 0 end) as status_4
from table
group by no
Max(case when status=2 then status else 0 end) as status_2,
Max(case when status=3 then status else 0 end) as status_3,
Max(case when status=4 then status else 0 end) as status_4
from table
group by no
(no varchar(1),
status int)insert into t
select 'A',1
union all
select 'A',2
union all
select 'A',2
union all
select 'B',1
union all
select 'B',3
union all
select 'C',4
union all
select 'C',4
select no,
status1 =(select count(status) from t as a where a.no = t.no and a.status = 1),
status2 =(select count(status) from t as a where a.no = t.no and a.status = 2),
status3 =(select count(status) from t as a where a.no = t.no and a.status = 3),
status4 =(select count(status) from t as a where a.no = t.no and a.status = 4)
from t
group by nodelete from tdrop table t
Create table T (no varchar(2), status varchar(10))
insert into T select 'A',1
union all select 'A',2
union all select 'A',2
union all select 'B',1
union all select 'B',3
union all select 'C',4
union all select 'C',4--查询
select no,
status_1=(select count(*) from T where no=A.no and status=1),
status_2=(select count(*) from T where no=A.no and status=2),
status_3=(select count(*) from T where no=A.no and status=3),
status_4=(select count(*) from T where no=A.no and status=4)
from T A
group by no--结果
no status_1 status_2 status_3 status_4
---- ----------- ----------- ----------- -----------
A 1 2 0 0
B 1 0 1 0
C 0 0 0 2--删除测试环境
Drop Table T
set @s='select no '
select @s=@s+',[status_'+status+']=(select count(*) from T where no=A.no and status='+status+')'
from T
group by status
set @s=@s+' from T A group by no order by no asc '
exec(@s)--结果
no status_1 status_2 status_3 status_4
---- ----------- ----------- ----------- -----------
A 1 2 0 0
B 1 0 1 0
C 0 0 0 2
create table A
( no varchar(10),
status int
)
insert A
select 'A',1 union all
select 'A',2 union all
select 'A',2 union all
select 'B',1 union all
select 'B',3 union all
select 'C',4 union all
select 'C',4 union
select T.no ,sum(case when T.status=1 then cou else 0 end) as status_1,
sum(case when T.status=2 then cou else 0 end) as status_2,
sum(case when T.status=3 then cou else 0 end) as status_3,
sum(case when T.status=4 then cou else 0 end) as status_4
from (select no,status,count(*) as cou from a group by no,status) T
group by T.no