SELECT * into #临时表 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Access文件";User ID=用户名;Password=密码')...Access表名declare @sql varchar(8000) set @sql = 'select 学生' select @sql = @sql + ',sum(case 课程编码 when '''+cast(课程编码 as varchar)+''' then 成绩 else 0 end) as ''课程'+cast(课程编码 as varchar)+'''' from (select distinct 课程编码 from #临时表) as a select @sql = @sql+' from #临时表 group by 学生'exec(@sql)drop table #临时表 go
交叉表啊 declare @sql varchar(8000) set @sql = 'select 学生,'select @sql = @sql + 'sum(case 课程编码 when '''+课程编码+''' then 成绩 else 0 end) as ['+课程+'],' from (select distinct 课程编码 from 表) aselect @sql = left(@sql,len(@sql)-1) + ' from 表 group by 学生'exec(@sql)
前几天答过呀?连数据都一样~! declare @ table(Kid varchar(10), Sid varchar(10),score int) insert into @ values('01' ,'01' ,60) insert into @ values('02' ,'01' ,70) insert into @ values('03' ,'01' ,52) insert into @ values('01' ,'02' ,45) insert into @ values('02' ,'02' ,89) insert into @ values('03' ,'04' ,78)select * from @ select * into temp from @declare @sql varchar(8000) set @sql = 'select sid as 学生编号' select @sql = @sql + ',sum(case kid when '''+kid+''' then score else 0 end) as 课程'+kid from (select distinct kid from temp) as a select @sql = @sql+' from temp group by sid'exec(@sql)drop table temp
set @sql = 'select 学生'
select @sql = @sql + ',sum(case 课程编码 when '''+cast(课程编码 as varchar)+''' then 成绩 else 0 end) as ''课程'+cast(课程编码 as varchar)+''''
from (select distinct 课程编码 from #临时表) as a
select @sql = @sql+' from #临时表 group by 学生'exec(@sql)drop table #临时表
go
set @sql = 'select 学生,'select @sql = @sql + 'sum(case 课程编码 when '''+课程编码+'''
then 成绩 else 0 end) as ['+课程+'],'
from (select distinct 课程编码 from 表) aselect @sql = left(@sql,len(@sql)-1) + ' from 表 group by 学生'exec(@sql)
declare @ table(Kid varchar(10), Sid varchar(10),score int)
insert into @ values('01' ,'01' ,60)
insert into @ values('02' ,'01' ,70)
insert into @ values('03' ,'01' ,52)
insert into @ values('01' ,'02' ,45)
insert into @ values('02' ,'02' ,89)
insert into @ values('03' ,'04' ,78)select * from @
select * into temp from @declare @sql varchar(8000)
set @sql = 'select sid as 学生编号'
select @sql = @sql + ',sum(case kid when '''+kid+''' then score else 0 end) as 课程'+kid
from (select distinct kid from temp) as a
select @sql = @sql+' from temp group by sid'exec(@sql)drop table temp