假设表名位table1
select name,a=sum(case when xm='a' then sj else 0 end)
b=sum(case when xm='b' then sj else 0 end)
from table1 aa
select name,a=sum(case when xm='a' then sj else 0 end)
b=sum(case when xm='b' then sj else 0 end)
from table1 aa
name,
a=(case when xm='a' then sj end),
b=(case when xm='b' then sj end)
from 表
insert into 表 select 'A','a',1.2
insert into 表 select 'A','b',2.3
insert into 表 select 'A','a',1.1
insert into 表 select 'A','b',2.2
insert into 表 select 'A','a',1.3
insert into 表 select 'A','b',2.4select identity(int,0,1) as id,* into #t from 表select
name,
a = max(case xm when 'a' then sj end),
b = max(case xm when 'b' then sj end)
from
#t
group by
id/2,name
drop table 表,#T
set @sql = 'select name'
select @sql = @sql +',['+ xm+']=sum(case xm when '''+ xm +''' then sj else 0 end)' from 表 group by xm
set @sql = @sql +' from tblA group by name'
exec(@sql)
set @sql = 'select name'
select @sql = @sql +',['+ xm+']=sum(case xm when '''+ xm +''' then sj else 0 end)' from 表 group by xm
set @sql = @sql +' from 表 group by name'
exec(@sql)
insert t1
select 'A','a',1.2 union all
select 'A','b',2.3 union all
select 'A','a',1.1 union all
select 'A','b',2.2 union all
select 'A','a',1.3 union all
select 'A','b',2.4
select * from t1
alter table t1 add sid int identity(1,1)
go
select [name],
a=sum(case xm when 'a' then sj end),
b=sum(case xm when 'b' then sj end)
from t1 group by name,(sid-1)/2
godrop table t1
/*
name xm sj
---- ---- -----------
A a 1.20
A b 2.30
A a 1.10
A b 2.20
A a 1.30
A b 2.40(所影响的行数为 6 行)
(所影响的行数为 6 行)name a b
---- ---------------------------------------- ----
A 1.20 2.30
A 1.10 2.20
A 1.30 2.40(所影响的行数为 3 行)警告: 聚合或其它 SET 操作消除了空值。*/