科目表只能固定好select a.studentID, case when a.courseID='001' then grade else 0 end as 语文, case when a.courseID='002' then grade else 0 end as 数学, ... c.major from grade a left join student b on a.studentID=b.studentID left join class c on b.classID=c.classID
补充一下: select a.studentID, case when a.courseID='001' then grade else 0 end as 语文, case when a.courseID='002' then grade else 0 end as 数学, ... c.major into #temp from grade a left join student b on a.studentID=b.studentID left join class c on b.classID=c.classIDselect studentID,max(语文) as 语文,max(数学) as 数学,...,major from #temp group by studentID,major
create table #course(courseID char(3),courseName varchar(20)) insert #course select '001','语文' insert #course select '002','数学'create table #grade(courseID char(3),studentID int,grade int) insert #grade select '001',2010, 97 insert #grade select '002',2010 ,98 create table #class(classID int,major varchar(20)) insert #class select 1, '信息管理' insert #class select 2, '计算机技术' create table #student(studentID int,classID int) insert #student select 2010,1 insert #student select 2011,2 declare @col varchar(100) set @col=STUFF((select ','+courseName from #course a where exists(select 1 from #grade where a.courseID=courseID) order by courseID for xml path('')),1,1,'') exec( 'select studentID,'+@col+' ,major from ( select a.studentID,d.courseName, c.grade,b.major from #student a inner join #class b on a.classID=b.classID inner join #grade c on a.studentID=c.studentID inner join #course d on c.courseID=d.courseID) a '+ 'pivot (max(grade) for courseName in('+@col+')) pvt' )studentID 语文 数学 major ----------- ----------- ----------- -------------------- 2010 97 98 信息管理(1 行受影响)
case when a.courseID='001' then grade else 0 end as 语文,
case when a.courseID='002' then grade else 0 end as 数学,
...
c.major from grade a
left join student b on a.studentID=b.studentID
left join class c on b.classID=c.classID
麻烦楼主看看
select a.studentID,
case when a.courseID='001' then grade else 0 end as 语文,
case when a.courseID='002' then grade else 0 end as 数学,
...
c.major
into #temp
from grade a
left join student b on a.studentID=b.studentID
left join class c on b.classID=c.classIDselect studentID,max(语文) as 语文,max(数学) as 数学,...,major from #temp
group by studentID,major
create table #course(courseID char(3),courseName varchar(20))
insert #course select '001','语文'
insert #course select '002','数学'create table #grade(courseID char(3),studentID int,grade int)
insert #grade select '001',2010, 97
insert #grade select '002',2010 ,98
create table #class(classID int,major varchar(20))
insert #class select 1, '信息管理'
insert #class select 2, '计算机技术'
create table #student(studentID int,classID int)
insert #student select 2010,1
insert #student select 2011,2
declare @col varchar(100)
set @col=STUFF((select ','+courseName from #course a where exists(select 1 from #grade where a.courseID=courseID)
order by courseID
for xml path('')),1,1,'')
exec(
'select studentID,'+@col+' ,major from (
select a.studentID,d.courseName, c.grade,b.major
from #student a
inner join #class b
on a.classID=b.classID
inner join #grade c
on a.studentID=c.studentID
inner join #course d
on c.courseID=d.courseID) a '+
'pivot (max(grade) for courseName in('+@col+')) pvt'
)studentID 语文 数学 major
----------- ----------- ----------- --------------------
2010 97 98 信息管理(1 行受影响)