create table tb(c0 varchar(10),c1 int,c2 int,c3 int,c4 int) insert into tb select 'A',0,1,10,1000 insert into tb select 'A',0,0,5,300 insert into tb select 'A',1,0,2,600 insert into tb select 'A',1,1,4,100 insert into tb select 'B',1,0,20,2000 insert into tb select 'C',0,1,30,200 go --静态的: select c0, sum(case when c1=1 and c2=0 then c3 else 0 end)[1-0-C3], sum(case when c1=1 and c2=1 then c3 else 0 end)[1-1-C3], sum(case when c1=0 and c2=1 then c3 else 0 end)[0-1-C3], sum(case when c1=0 and c2=0 then c3 else 0 end)[0-0-C3], sum(case when c1=1 and c2=0 then c4 else 0 end)[1-0-C4], sum(case when c1=1 and c2=1 then c4 else 0 end)[1-1-C4], sum(case when c1=0 and c2=1 then c4 else 0 end)[0-1-C4], sum(case when c1=0 and c2=0 then c4 else 0 end)[0-0-C4] from tb group by c0 /* c0 1-0-C3 1-1-C3 0-1-C3 0-0-C3 1-0-C4 1-1-C4 0-1-C4 0-0-C4 ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- A 2 4 10 5 600 100 1000 300 B 20 0 0 0 2000 0 0 0 C 0 0 30 0 0 0 200 0(3 行受影响)*/ go drop table tb
select c0, max(case when c1=1 and c2=0 then c3 else 0 end) as [1-0-c3], max(case when c1=1 and c2=1 then c3 else 0 end) as [1-1-c3], max(case when c1=0 and c2=1 then c3 else 0 end) as [0-1-c3], max(case when c1=1 and c2=0 then c4 else 0 end) as [1-0-c4], max(case when c1=1 and c2=1 then c4 else 0 end) as [1-1-c4], max(case when c1=0 and c2=1 then c4 else 0 end) as [0-1-c4], max(case when c1=0 and c2=0 then c4 else 0 end) as [0-0-c4] from tb group by c0
insert into tb select 'A',0,1,10,1000
insert into tb select 'A',0,0,5,300
insert into tb select 'A',1,0,2,600
insert into tb select 'A',1,1,4,100
insert into tb select 'B',1,0,20,2000
insert into tb select 'C',0,1,30,200
go
--静态的:
select c0,
sum(case when c1=1 and c2=0 then c3 else 0 end)[1-0-C3],
sum(case when c1=1 and c2=1 then c3 else 0 end)[1-1-C3],
sum(case when c1=0 and c2=1 then c3 else 0 end)[0-1-C3],
sum(case when c1=0 and c2=0 then c3 else 0 end)[0-0-C3],
sum(case when c1=1 and c2=0 then c4 else 0 end)[1-0-C4],
sum(case when c1=1 and c2=1 then c4 else 0 end)[1-1-C4],
sum(case when c1=0 and c2=1 then c4 else 0 end)[0-1-C4],
sum(case when c1=0 and c2=0 then c4 else 0 end)[0-0-C4]
from tb
group by c0
/*
c0 1-0-C3 1-1-C3 0-1-C3 0-0-C3 1-0-C4 1-1-C4 0-1-C4 0-0-C4
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A 2 4 10 5 600 100 1000 300
B 20 0 0 0 2000 0 0 0
C 0 0 30 0 0 0 200 0(3 行受影响)*/
go
drop table tb
c0,
max(case when c1=1 and c2=0 then c3 else 0 end) as [1-0-c3],
max(case when c1=1 and c2=1 then c3 else 0 end) as [1-1-c3],
max(case when c1=0 and c2=1 then c3 else 0 end) as [0-1-c3],
max(case when c1=1 and c2=0 then c4 else 0 end) as [1-0-c4],
max(case when c1=1 and c2=1 then c4 else 0 end) as [1-1-c4],
max(case when c1=0 and c2=1 then c4 else 0 end) as [0-1-c4],
max(case when c1=0 and c2=0 then c4 else 0 end) as [0-0-c4]
from
tb
group by
c0