27,581
社区成员
发帖
与我相关
我的任务
分享Help
--drop table test2
这个存储过程报错，如何修改，谢谢了
Create table test2(name char(10),km char(10),cj int)
go
insert test2 values('张三','语文',80)
insert test2 values('张三','数学',86)
insert test2 values('张三','英语',75)
insert test2 values('李四','语文',78)
insert test2 values('李四','数学',85)
insert test2 values('李四','英语',78)
insert test2 values('李四','历史',78)
--Select * from test2
Alter proc aa
as
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then isnull(cj,0) end)['+km+']'
from (select distinct km from test2) as a
select @sql = @sql+',sum(cj) as total2 into #a from test2 group by name'
--print @sql
exec(@sql)
Select * from #a
drop table #a
select name,sum(case km when '历史 ' then cj end)[历史 ],
sum(case km when '数学 ' then cj end)[数学 ],
sum(case km when '英语 ' then cj end)[英语 ],
sum(case km when '语文 ' then cj end)[语文 ],
sum(cj) as total2 into #a from test2 group by name
这个存储过程报错，如何修改，谢谢了
Create table test2(name char(10),km char(10),cj int)
go
insert test2 values('张三','语文',80)
insert test2 values('张三','数学',86)
insert test2 values('张三','英语',75)
insert test2 values('李四','语文',78)
insert test2 values('李四','数学',85)
insert test2 values('李四','英语',78)
insert test2 values('李四','历史',78)
--Select * from test2
Alter proc aa
as
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then isnull(cj,0) end)['+km+']'
from (select distinct km from test2) as a
select @sql = @sql+',sum(cj) as total2 into #a from test2 group by name'
--print @sql
exec(@sql)
Select * from #a
drop table #a
select name,sum(case km when '历史 ' then cj end)[历史 ],
sum(case km when '数学 ' then cj end)[数学 ],
sum(case km when '英语 ' then cj end)[英语 ],
sum(case km when '语文 ' then cj end)[语文 ],
sum(cj) as total2 into #a from test2 group by name
...全文
请发表友善的回复…
发表回复
wishton_126 2004-11-08
- 打赏
- 举报
这个存储过程报错，如何修改，谢谢了
Create table test2(name char(10),km char(10),cj int)
go
insert test2 values('张三','语文',80)
insert test2 values('张三','数学',86)
insert test2 values('张三','英语',75)
insert test2 values('李四','语文',78)
insert test2 values('李四','数学',85)
insert test2 values('李四','英语',78)
insert test2 values('李四','历史',78)
--Select * from test2
Alter proc aa
as
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then isnull(cj,0) end)['+km+']'
from (select distinct km from test2) as a
select @sql = @sql+',sum(cj) as total2 into #a from test2 group by name'
--print @sql
exec(@sql)
Select * from #a
drop table #a
Create table test2(name char(10),km char(10),cj int)
go
insert test2 values('张三','语文',80)
insert test2 values('张三','数学',86)
insert test2 values('张三','英语',75)
insert test2 values('李四','语文',78)
insert test2 values('李四','数学',85)
insert test2 values('李四','英语',78)
insert test2 values('李四','历史',78)
--Select * from test2
Alter proc aa
as
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then isnull(cj,0) end)['+km+']'
from (select distinct km from test2) as a
select @sql = @sql+',sum(cj) as total2 into #a from test2 group by name'
--print @sql
exec(@sql)
Select * from #a
drop table #a
