declare @s varchar(8000)
set @s=''
select @s=@s+',['+CourseWeek+']=max(case CourseWeek when '''+CourseWeek+''' then CourseID end)'
from tbl_Class_Course group by CourseWeek order by CourseWeek desc
exec('select 课程表'+@s+'
from tbl_Class_Course
group by 课程表')
set @s=''
select @s=@s+',['+CourseWeek+']=max(case CourseWeek when '''+CourseWeek+''' then CourseID end)'
from tbl_Class_Course group by CourseWeek order by CourseWeek desc
exec('select 课程表'+@s+'
from tbl_Class_Course
group by 课程表')
----------------------------------------------------------------------------
select
课程表 = (case type when 1 then a.课程号 else null end),
a.星期一,a.星期二,a.星期三,a.星期四,a.星期五,a.星期六
from
(select
课程号 = case CourseOrder
when 1 then '1.2节'
when 2 then '3.4节'
when 3 then '5.6节'
when 4 then '7.8节'
end,
星期一 = max(case CourseWeek when 1 then CourseID end),
星期二 = max(case CourseWeek when 2 then CourseID end),
星期三 = max(case CourseWeek when 3 then CourseID end),
星期四 = max(case CourseWeek when 4 then CourseID end),
星期五 = max(case CourseWeek when 5 then CourseID end),
星期六 = max(case CourseWeek when 6 then CourseID end),
type = 1
from
tbl_Class_Course
group by
CourseOrder
union all
select
课程号 = case CourseOrder
when 1 then '1.2节'
when 2 then '3.4节'
when 3 then '5.6节'
when 4 then '7.8节'
end,
星期一 = max(case CourseWeek when 1 then RoomID end),
星期二 = max(case CourseWeek when 2 then RoomID end),
星期三 = max(case CourseWeek when 3 then RoomID end),
星期四 = max(case CourseWeek when 4 then RoomID end),
星期五 = max(case CourseWeek when 5 then RoomID end),
星期六 = max(case CourseWeek when 6 then RoomID end),
type = 2
from
tbl_Class_Course
group by
CourseOrder
union all
select
课程号 = case CourseOrder
when 1 then '1.2节'
when 2 then '3.4节'
when 3 then '5.6节'
when 4 then '7.8节'
end,
星期一 = max(case CourseWeek when 1 then TeacherNO end),
星期二 = max(case CourseWeek when 2 then TeacherNO end),
星期三 = max(case CourseWeek when 3 then TeacherNO end),
星期四 = max(case CourseWeek when 4 then TeacherNO end),
星期五 = max(case CourseWeek when 5 then TeacherNO end),
星期六 = max(case CourseWeek when 6 then TeacherNO end),
type = 3
from
tbl_Class_Course
group by
CourseOrder) a
order by
a.课程表,a.type
----------------------------------------------------------------------------
select
课程表 = (case type when 1 then a.课程号 else null end),
a.星期一,a.星期二,a.星期三,a.星期四,a.星期五,a.星期六
from
(select
课程号 = case CourseOrder
when 1 then '1.2节'
when 2 then '3.4节'
when 3 then '5.6节'
when 4 then '7.8节'
end,
星期一 = max(case CourseWeek when 1 then CourseID end),
星期二 = max(case CourseWeek when 2 then CourseID end),
星期三 = max(case CourseWeek when 3 then CourseID end),
星期四 = max(case CourseWeek when 4 then CourseID end),
星期五 = max(case CourseWeek when 5 then CourseID end),
星期六 = max(case CourseWeek when 6 then CourseID end),
type = 1
from
tbl_Class_Course
group by
CourseOrder
union all
select
课程号 = case CourseOrder
when 1 then '1.2节'
when 2 then '3.4节'
when 3 then '5.6节'
when 4 then '7.8节'
end,
星期一 = max(case CourseWeek when 1 then RoomID end),
星期二 = max(case CourseWeek when 2 then RoomID end),
星期三 = max(case CourseWeek when 3 then RoomID end),
星期四 = max(case CourseWeek when 4 then RoomID end),
星期五 = max(case CourseWeek when 5 then RoomID end),
星期六 = max(case CourseWeek when 6 then RoomID end),
type = 2
from
tbl_Class_Course
group by
CourseOrder
union all
select
课程号 = case CourseOrder
when 1 then '1.2节'
when 2 then '3.4节'
when 3 then '5.6节'
when 4 then '7.8节'
end,
星期一 = max(case CourseWeek when 1 then TeacherNO end),
星期二 = max(case CourseWeek when 2 then TeacherNO end),
星期三 = max(case CourseWeek when 3 then TeacherNO end),
星期四 = max(case CourseWeek when 4 then TeacherNO end),
星期五 = max(case CourseWeek when 5 then TeacherNO end),
星期六 = max(case CourseWeek when 6 then TeacherNO end),
type = 3
from
tbl_Class_Course
group by
CourseOrder) a
order by
a.课程表,a.type
[ClassCourseID] [int] IDENTITY (1, 1) NOT NULL ,
[ClassNO] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CourseID] [int] NULL ,
[TeacherNO] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[RoomID] [int] NULL ,
[CourseOrder] [int] NULL ,
[CourseWeek] [int] NULL
) ON [PRIMARY]
GOinsert into tbl_Class_Course (ClassNO,CourseID,TeacherNO,RoomID,CourseOrder,CourseWeek)
values('02001',2,'0255',1,2,1)
insert into tbl_Class_Course (ClassNO,CourseID,TeacherNO,RoomID,CourseOrder,CourseWeek)
values('03002',3,'0352',2,3,2)
得到这样的结果:
课程表 星期一 星期二 星期三 星期四 星期五 星期六
NULL 1 NULL NULL NULL NULL NULL
NULL NULL 2 NULL NULL NULL NULL
NULL 255 NULL NULL NULL NULL NULL
NULL NULL 352 NULL NULL NULL NULL
3.4节 2 NULL NULL NULL NULL NULL
5.6节 NULL 3 NULL NULL NULL NULL我想要得到的结果是这样的:课程表 星期一 星期二 星期三 星期四 星期五 星期六
1.2节 CourseID CourseID
RoomID RoomID
TeacherNO TeacherNO
3.4节 CourseID
RoomID
TeacherNO
5.6节
------------------------------------------------------------------------------
select
课程表 = (case a.type when 1 then a.CourseDesc else null end),
b.星期一,b.星期二,b.星期三,b.星期四,b.星期五,b.星期六
from
(select 1 as CourseOrder,'1.2节' as CourseDesc,1 as type
union
select 1 as CourseOrder,'1.2节' as CourseDesc,2 as type
union
select 1 as CourseOrder,'1.2节' as CourseDesc,3 as type
union
select 2 as CourseOrder,'3.4节' as CourseDesc,1 as type
union
select 2 as CourseOrder,'3.4节' as CourseDesc,2 as type
union
select 2 as CourseOrder,'3.4节' as CourseDesc,3 as type
union
select 3 as CourseOrder,'5.6节' as CourseDesc,1 as type
union
select 3 as CourseOrder,'5.6节' as CourseDesc,2 as type
union
select 3 as CourseOrder,'5.6节' as CourseDesc,3 as type
union
select 4 as CourseOrder,'7.8节' as CourseDesc,1 as type
union
select 4 as CourseOrder,'7.8节' as CourseDesc,2 as type
union
select 4 as CourseOrder,'7.8节' as CourseDesc,3 as type
) a
left join
(select
CourseOrder,
星期一 = max(case CourseWeek when 1 then CourseID end),
星期二 = max(case CourseWeek when 2 then CourseID end),
星期三 = max(case CourseWeek when 3 then CourseID end),
星期四 = max(case CourseWeek when 4 then CourseID end),
星期五 = max(case CourseWeek when 5 then CourseID end),
星期六 = max(case CourseWeek when 6 then CourseID end),
type = 1
from
tbl_Class_Course
group by
CourseOrder
union all
select
CourseOrder,
星期一 = max(case CourseWeek when 1 then RoomID end),
星期二 = max(case CourseWeek when 2 then RoomID end),
星期三 = max(case CourseWeek when 3 then RoomID end),
星期四 = max(case CourseWeek when 4 then RoomID end),
星期五 = max(case CourseWeek when 5 then RoomID end),
星期六 = max(case CourseWeek when 6 then RoomID end),
type = 2
from
tbl_Class_Course
group by
CourseOrder
union all
select
CourseOrder,
星期一 = max(case CourseWeek when 1 then TeacherNO end),
星期二 = max(case CourseWeek when 2 then TeacherNO end),
星期三 = max(case CourseWeek when 3 then TeacherNO end),
星期四 = max(case CourseWeek when 4 then TeacherNO end),
星期五 = max(case CourseWeek when 5 then TeacherNO end),
星期六 = max(case CourseWeek when 6 then TeacherNO end),
type = 3
from
tbl_Class_Course
group by
CourseOrder) b
on
a.CourseOrder = b.CourseOrder and a.type = b.type
order by
a.CourseDesc,a.type
------ ------ ------ ------ ------ ------ ------
1.2节 NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL
3.4节 2 NULL NULL NULL NULL NULL
NULL 1 NULL NULL NULL NULL NULL
NULL 255 NULL NULL NULL NULL NULL
5.6节 NULL 3 NULL NULL NULL NULL
NULL NULL 2 NULL NULL NULL NULL
NULL NULL 352 NULL NULL NULL NULL
7.8节 NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL