参考:create table cjb(姓名 char (10),课程名 char (10),成绩 [decimal](12, 2)) insert into cjb select '张','数据库', 78 union select '张','信息管理', 80 union select '张','专业英语', 89 union select '李','数据库', 90 union select '李','信息管理', 67 union select '李','专业英语', 56 go declare @sql varchar(8000) set @sql='' select @sql=@sql + ',['+rtrim(课程名)+']=max(case 课程名 when '''+rtrim(课程名)+''' then rtrim(成绩) end)' from cjb group by 课程名 exec('select 姓名'+@sql+'from cjb group by 姓名' )
---------------------------------------------------------------- -- 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 */
insert into cjb
select '张','数据库', 78 union
select '张','信息管理', 80 union
select '张','专业英语', 89 union
select '李','数据库', 90 union
select '李','信息管理', 67 union
select '李','专业英语', 56
go
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(课程名)+']=max(case 课程名 when '''+rtrim(课程名)+''' then rtrim(成绩) end)'
from cjb group by 课程名
exec('select 姓名'+@sql+'from cjb group by 姓名' )
-- 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
*/