----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-12-23 14:55:07
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([学生ID] int,[课程] varchar(8),[成绩] int)
insert [a]
select 1,'英语',60 union all
select 1,'数学',66 union all
select 1,'体育',77 union all
select 1,'网络基础',88 union all
select 1,'计算机',99 union all
select 2,'英语',55 union all
select 2,'数学',66 union all
select 2,'体育',77 union all
select 2,'网络基础',88 union all
select 2,'计算机',67 union all
select 3,'英语',55 union all
select 3,'数学',66 union all
select 3,'体育',77 union all
select 3,'网络基础',88 union all
select 3,'计算机',67
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([ID] int,[学生ID] int,[理论考试] int,[操作考试] int)
insert [b]
select 1,1,66,77 union all
select 1,2,87,67 union all
select 1,3,65,76
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select a.学生ID,b.理论考试,b.操作考试'
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 成绩 else 0 end) [' + 课程+ ']'
from (select distinct 课程 from a) as a
set @sql = @sql + ',avg(a.成绩) as 综合成绩 from a join b on a.学生ID=b.学生ID group by a.学生ID,a.学生ID,b.理论考试,b.操作考试'
exec(@sql)
----------------结果----------------------------
/* 学生ID        理论考试        操作考试        计算机         数学          体育          网络基础        英语          综合成绩
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           66          77          99          66          77          88          60          78
2           87          67          67          66          77          88          55          70
3           65          76          67          66          77          88          55          70

(3 行受影响)

*/

------解决方案--------------------

SQL code

create table tab1(学生ID int, 课程 varchar(10), 成绩 int)

insert into tab1
select 1, '英语', 60 union all
select 1, '数学', 66 union all
select 1, '体育', 77 union all
select 1, '网络基础', 88 union all
select 1, '计算机', 99 union all
select 2, '英语', 55 union all
select 2, '数学', 66 union all
select 2, '体育', 77 union all
select 2, '网络基础', 88 union all
select 2, '计算机', 67 union all
select 3, '英语', 55 union all
select 3, '数学', 66 union