declare @sql varchar(8000)
set @sql = 'select ProductName'
select @sql = @sql + ',sum(case Producttype when '''+ProductType+''' then SumPrice end) ['+ProductType+']',sum(SumPrice) as SumPrice
from (select distinct ProductType from 表) as a
select @sql = @sql+' from 表 group by ProductName'
exec(@sql)
set @sql = 'select ProductName'
select @sql = @sql + ',sum(case Producttype when '''+ProductType+''' then SumPrice end) ['+ProductType+']',sum(SumPrice) as SumPrice
from (select distinct ProductType from 表) as a
select @sql = @sql+' from 表 group by ProductName'
exec(@sql)
set @sql = 'select ProductName'
select @sql = @sql + ',sum(case Producttype when '''+ProductType+''' then SumPrice end) ['+ProductType+']'
from (select distinct ProductType from 表 order by ProductType) as a
select @sql = @sql+',sum(SumPrice) as SumPrice from 表 group by ProductName'
exec(@sql)
set @sql = 'select ProductName'
select @sql = @sql + ',sum(case Producttype when '''+ProductType+''' then SumPrice end) ['+ProductType+']'
from (select distinct ProductType from 表) as a
select @sql = @sql+',sum(SumPrice) as SumPrice from 表 group by ProductName'
exec(@sql)妙
select productname,
sum(case producttype when type_a then sumprice else 0 end) [producttype],
sum(case producttype when type_b then sumprice else 0 end) [producttype],
sum(sumprice) as sumprice
from table group by productname
insert 你的表 values('n1','aa',11)
insert 你的表 values('n2','bb',1)
insert 你的表 values('n3','aa',45)
insert 你的表 values('n4','cc',81)
insert 你的表 values('n5','a',11)
insert 你的表 values('n6','aay',561)
insert 你的表 values('n7','a',14)declare @sql varchar(8000)
set @sql = 'select 部门'
select @sql = @sql + ',sum(case 学历 when '''+学历+''' then 1 else 0 end) ['+学历+'的人数]'
from (select distinct 学历 from 你的表) aselect @sql =@sql+' from 你的表 group by 部门'exec(@sql)
drop table t1
go
create table t1(ProductName varchar(10), ProductType varchar(10),SumPrice numeric(10,1))
insert t1 select 'Product_A','Type_A', 15.0
union all select 'Product_A','Type_B', 20.0
union all select 'Product_B','Type_A', 50.0declare @sql varchar(8000)
set @sql = 'select ProductName'
select @sql = @sql + ',sum(case Producttype when '''+ProductType+''' then SumPrice end) ['+ProductType+']'
from (select distinct ProductType from t1) as a
select @sql = @sql+',sum(SumPrice) as SumPrice from t1 group by ProductName'
exec(@sql)