求数据库双表合并及行转列的SQL语句。
一个是成绩表（动态，注意有考试所属次数）
最后我希望根据如上两个表得到下面两个表：
学生和期数的各科成绩表
学生和科目的各期成绩表。
我不知道如何来完成，不过我觉得应该有可能。有大侠指点一下？ 数据库 合并 结构
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-28 15:56:05
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[科目表]
if object_id('[科目表]') is not null drop table [科目表]
go
create table [科目表]([科目ID] int,[科目名称] varchar(4))
insert [科目表]
select 1,'数学' union all
select 2,'语文' union all
select 3,'物理' union all
select 4,'化学'
--> 测试数据：[成绩表]
if object_id('[成绩表]') is not null drop table [成绩表]
go
create table [成绩表]([学生id] int,[科目id] int,[考试期数] int,[成绩] int)
insert [成绩表]
select 1,1,1,80 union all
select 1,2,1,90 union all
select 1,3,1,60 union all
select 2,1,1,66 union all
select 2,1,1,78 union all
select 1,1,2,70 union all
select 2,1,2,67
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(科目名称)+'=max(case when [科目名称]='+quotename(科目名称,'''')+' then [成绩] else 0 end)'
from (select 学生id,a.科目id,考试期数,成绩,科目名称
from [成绩表] a LEFT JOIN [科目表] b ON a.科目id=b.科目id ) a group by 科目名称
exec('select [学生ID],考试期数'+@s+' from (select 学生id,a.科目id,考试期数,成绩,科目名称
from [成绩表] a LEFT JOIN [科目表] b ON a.科目id=b.科目id) a
group by [学生ID],考试期数')
declare @s1 nvarchar(4000)
set @s1=''
Select @s1=@s1+','+quotename('期数'+CONVERT(CHAR(1),考试期数))+'=max(case when [考试期数]='+quotename(考试期数,'''')+' then [成绩] else 0 end)'
from (select 学生id,a.科目id,考试期数,成绩,科目名称
from [成绩表] a LEFT JOIN [科目表] b ON a.科目id=b.科目id ) a group by 考试期数
exec('select [学生ID],科目名称'+@s1+' from (select 学生id,a.科目id,考试期数,成绩,科目名称
from [成绩表] a LEFT JOIN [科目表] b ON a.科目id=b.科目id) a
group by [学生ID],科目名称
order by 学生id')
----------------结果----------------------------
/*
学生ID 考试期数 数学 物理 语文
----------- ----------- ----------- ----------- -----------
1 1 80 60 90
2 1 78 0 0
1 2 70 0 0
2 2 67 0 0
(4 row(s) affected)
学生ID 科目名称 期数1 期数2
----------- ---- ----------- -----------
1 数学 80 70
1 物理 60 0
1 语文 90 0
2 数学 78 67
*/
