SQL Server中的行转列

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