cname
1 75.5 语文
2 80.5 数学
3 90 体育
4 95 美术
5 57 心理这样的表 我想让他显示出来语文 数学 体育 美术 心理
75.5 80.5 90 95 57怎么写存储过程...
1 75.5 语文
2 80.5 数学
3 90 体育
4 95 美术
5 57 心理这样的表 我想让他显示出来语文 数学 体育 美术 心理
75.5 80.5 90 95 57怎么写存储过程...
调试欢乐多
create table #(id int identity(1,1),kh char(4),dh char(2),df numeric(18,0))
insert into #(kh,dh,df)
select '0001','01',60 union all
select '0001','02',80 union all
select '0001','03',90 union all
select '0001','04',100 union all
select '0002','01',50 union all
select '0002','02',40 union all
select '0002','03',60 union all
select '0002','04',90
select kh,sum(case when dh = '01' then df end) as dh1,
sum(case when dh = '02' then df end) as dh2,
sum(case when dh = '03' then df end) as dh3,
sum(case when dh = '04' then df end) as dh4,
sum(df) as zf,
identity(int,1,1) as mc
into #1
from #
group by kh
select * from #1
drop table #
drop table #1
insert into tb select 1,75.5 ,'语文'
union all select 2 ,80.5 ,'数学'
union all select 3 ,90 ,'体育'
union all select 4 ,95 ,'美术'
union all select 5 ,57 ,'心理'declare @sql varchar(8000)
set @sql='select '
select @sql=@sql+',['+cname+']=sum(case cname when '''+cname+''' then else 0 end)' from tb
set @sql=stuff(@sql,8,1,'')
exec(@sql+' from tb')drop table tb
insert into tb select 1,75.5 ,'语文'
union all select 2 ,80.5 ,'数学'
union all select 3 ,90 ,'体育'
union all select 4 ,95 ,'美术'
union all select 5 ,57 ,'心理'declare @sql varchar(8000)
set @sql='select '
select @sql=@sql+',['+cname+']=max(case cname when '''+cname+''' then else 0 end)' from tb
set @sql=stuff(@sql,8,1,'')
exec(@sql+' from tb')drop table tb