关于课程表的查询语句（200分）~~~~~~~！！！！！！

Question

--生成测试数据
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,1,1,'语文'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,1,2,'数学'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,1,3,'物理'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,1,4,'化学'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,2,1,'政治'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,2,2,'生物'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,2,3,'历史'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,2,4,'地理'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,3,1,'体育'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,3,2,'音乐'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,3,3,'美术'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,3,4,'英语'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,4,1,'劳动'
--执行查询
declare @s varchar(8000)
set @s = ''
select 
    @s = @s + ',['+a.item+'] = max(case when WeekDay = '+rtrim(WeekDay)+' and Serial = '+rtrim(Serial)+' then Subject end)'
from
   (select 
        WeekDay,
        Serial,
        ('星期'+rtrim(WeekDay)+'第'+rtrim(Serial)+'节') as item 
    from 
        Curriculum 
    group by 
        WeekDay,Serial) a
order by
    a.WeekDay,a.Serial
set @s = 'select ''单位''+GradeCode+''年级''+ClassCode+''班'' as 班 ' + @s + ' from Curriculum group by UnitCode,GradeCode,ClassCode'exec(@s)

Answer 1

insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select '1','1','1',1,1,'语文'
Union All
select '1','2','1',2,2,'物理'
Union All
select '2','1','2',4,4,'数学'Declare @str varchar(1000)
set @str=''
select @str=@str+',[星期'+cast(weekDay as char(1))+'第'+cast(Serial as char(2))+'节]=max(case weekday+''''+serial when '+cast(weekday as char(1))+'+''''+'+cast(serial as char(2))+' then subject else '''' end) ' from Curriculum Group by Weekday,Serialexec('select (''单位''+UnitCode+''年级''+GradeCode+''班级''+ClassCode) as 班级 '+@str+'from Curriculum Group by UnitCode,GradeCode,ClassCode')

Answer 2

declare @s varchar(8000)
set @s = ''
select 
    @s = @s + ',['+a.item+'] = max(case when WeekDay = '+rtrim(WeekDay)+' and Serial = '+rtrim(Serial)+' then Subject end)'
from
   (select 
        WeekDay,
        Serial,
        ('星期'+rtrim(WeekDay)+'第'+rtrim(Serial)+'节') as item 
    from 
        Curriculum 
    group by 
        WeekDay,Serial) a
order by
    a.WeekDay,a.Serial
set @s = 'select ''单位''+GradeCode+''年级''+ClassCode+''班'' as 班 ' + @s + ' from Curriculum group by UnitCode,GradeCode,ClassCode'exec(@s)