create procedure sp_getresult
as
begin
declare @s varchar(8000)
set @s = ''
select
@s = @s+',['+rtrim(e2)+']=sum(case e2 when '+rtrim(e2)+' then e3 else 0 end)'
from
table1
group by
e2
set @s = 'select e1'+@s+' from table1 group by e1 order by e1'
exec(@s)
end
goexec sp_getresult
go
as
begin
declare @s varchar(8000)
set @s = ''
select
@s = @s+',['+rtrim(e2)+']=sum(case e2 when '+rtrim(e2)+' then e3 else 0 end)'
from
table1
group by
e2
set @s = 'select e1'+@s+' from table1 group by e1 order by e1'
exec(@s)
end
goexec sp_getresult
go
create table T_Test (e1 varchar(10),e2 varchar(10), e3 int)
insert into T_Test select 'A',1,10
union all select 'A',2,20
union all select 'B',1,5
union all select 'C',2,10
union all select 'A',1,60declare @s varchar(2000)
set @s=' select 类别=e1'
select @s=@s+',['+e2+']=sum(case when e2='+e2+' then e3 else 0 end )'
from T_Test
group by e2
set @s=@s+'from T_Test T Group by e1'
exec(@s)
--结果
类别 1 2
---------- ----------- -----------
A 70 20
B 5 0
C 0 10