declare @sql varchar(8000) set @sql = 'select 学生号' select @sql = @sql + 'max(case 课程Id when '''+cast(课程Id as varchar)+''' then 课程Id end) ['+cast(课程Id as varchar)+'],sum(case 课程Id when '''+cast(课程Id as varchar)+''' then 成绩 else 0 end) as 成绩' from (select distinct 课程Id from 有一表) as a select @sql = @sql+' from 有一表 group by 学生号'exec(@sql) go
declare @sql varchar(8000) set @sql = 'select 学生号 , ' select @sql = @sql + 'max(case 课程Id when '''+cast(课程Id as varchar)+''' then 课程Id end) ['+cast(课程Id as varchar)+'],sum(case 课程Id when ''' +cast(课程Id as varchar)+''' then 成绩 else 0 end) as 成绩,' from (select distinct 课程Id from #r) as a select @sql = left(@sql,len(@sql)-1) +' from #r group by 学生号' exec(@sql)
declare @kc char(20),@cj char(20) declare @sql varchar(8000) set @sql='select 学生号,'declare cur cursor for select 学生号,课程id,成绩 from table where 学生号 in (select distinct 学生号 from table) open cur fetch next from cur into @kc,@cj while @@fetch_status=0 begin @sql=@sql+'(case 课程id when 课程id then @kc end ) as 课程id, '+ '(case 课程id when 课程id then @cj end ) as 成绩 ,' fetch next from cur into @kc,@cj end close curselect @sql=left(@sql,len(@sql)-1)+'from table where 学生号 in (select distinct 学生号 from table) group by 学生号'exec(@sql)
set @sql = 'select 学生号'
select @sql = @sql + 'max(case 课程Id when '''+cast(课程Id as varchar)+''' then 课程Id end) ['+cast(课程Id as varchar)+'],sum(case 课程Id when '''+cast(课程Id as varchar)+''' then 成绩 else 0 end) as 成绩'
from (select distinct 课程Id from 有一表) as a
select @sql = @sql+' from 有一表 group by 学生号'exec(@sql)
go
set @sql = 'select 学生号 , '
select @sql = @sql + 'max(case 课程Id when '''+cast(课程Id as varchar)+'''
then 课程Id end) ['+cast(课程Id as varchar)+'],sum(case 课程Id when '''
+cast(课程Id as varchar)+''' then 成绩 else 0 end) as 成绩,'
from (select distinct 课程Id from #r) as a
select @sql = left(@sql,len(@sql)-1) +' from #r group by 学生号'
exec(@sql)
declare @sql varchar(8000)
set @sql='select 学生号,'declare cur cursor for
select 学生号,课程id,成绩 from table where 学生号 in (select distinct 学生号 from
table)
open cur
fetch next from cur
into @kc,@cj
while @@fetch_status=0
begin
@sql=@sql+'(case 课程id when 课程id then @kc end ) as 课程id, '+ '(case 课程id when 课程id then @cj end ) as 成绩 ,'
fetch next from cur
into @kc,@cj
end
close curselect @sql=left(@sql,len(@sql)-1)+'from table where 学生号 in (select distinct 学生号 from table) group by 学生号'exec(@sql)