with tt as(select s.MorOrAftOrEve, case s.MorOrAftOrEve when 1 then '上午' when 2 then '下午' else '晚上' end as MAE ,'第'+cast(s.Section as varchar(10))+'节' as JC,

(CASE s.WeekDay when '星期一' then s.ProjectID else 0 end) as week1,

(CASE s.WeekDay when '星期二' then s.ProjectID else 0 end) as week2,

(CASE s.WeekDay when '星期三' then s.ProjectID else 0 end) as week3,

(CASE s.WeekDay when '星期四' then s.ProjectID else 0 end) as week4,

(CASE s.WeekDay when '星期五' then s.ProjectID else 0 end) as week5,

(CASE s.WeekDay when '星期六' then s.ProjectID else 0 end) as week6,

(CASE s.WeekDay when '星期日' then s.ProjectID else 0 end) as week7

from dbo.EDU_ScheduleManager s where s.ClassID=#ClassID# and s.SchoolID=#SchoolID#)

select mae,jc,

(select CourseName from dbo.EDU_Course where id=sum(week1)) as w1,

(select CourseName from dbo.EDU_Course where id=SUM(week2)) as w2,

(select CourseName from dbo.EDU_Course where id=SUM(week3)) as w3,

(select CourseName from dbo.EDU_Course where id=SUM(week4)) as w4,

(select CourseName from dbo.EDU_Course where id=SUM(week5)) as w5,

(select CourseName from dbo.EDU_Course where id=SUM(week6)) as w6,

(select CourseName from dbo.EDU_Course where id=SUM(week7)) as w7 from tt

group by mae,jc,MorOrAftOrEve order by MorOrAftOrEve asc