如果AF#是字符型,你可以将sum改为max,0改为''
declare @s varchar(8000)
set @s=''
select @s=@s+',['+AF1+']=max(case AF1 when '''+AF1+''' then AF3 else '''' end)'
from A表
group by AF1
exec('select id'+@s+' from A表 group by id')
第一种方法
select a.id,R1=a.AF3,R2=b.AF3 --你凭什么取a.id,
--我肯定要返回组数最多的那一种,假如R1有四组,
--R2有五组,我就要用b.id我根本不知道哪个行数最
from A表 a join A表 b on a.AF2=b.AF2
where a.AF1='R1' and b.AF1='R2'
第二种方法,假如我的AF3是字符型,并且是象'fff', 'ddd'这样的,无能为力了,
并且@s varchar(8000),我不喜欢,很大隐患,完全有可能超过8000
declare @s varchar(8000)
set @s=''
select @s=@s+',['+AF1+']=sum(case AF1 when '''+AF1+''' then AF3 else 0 end)'
from A表
group by AF1
exec('select id'+@s+' from A表 group by id')
--如果AF1的值是不固定的,就用动态SQL语句
declare @s varchar(8000)
set @s=''
select @s=@s+',['+AF1+']=sum(case AF1 when '''+AF1+''' then AF3 else 0 end)'
from A表
group by AF1
exec('select id'+@s+' from A表 group by id')
create table t1(af1 varchar(10),af2 int,af3 float)
insert t1
select 'r1',1,1.1
union all select 'r1',2,1.2
union all select 'r2',1,1.3
union all select 'r2',2,1.4
drop table t1
--select * from t1
select * into b from(select af2,sum(case af1 when 'r1' then af3 else 0 end) as r1,
sum(case af1 when 'r2' then af3 else 0 end) as r2
from t1
group by af2)a