求动态SQL语句

[小豆好好]

假设有张学生课程表(tb)如下:
姓名 课程 教室
张三 语文 101教室
张三 数学 202教室
张三 物理 101教室
李四 语文 202教室
李四 数学 101教室
李四 物理 202教室

教室表（kc）如下：

教室 地点 座位 状态
---- ---- ---- ----
101教室 教西一楼 22 在用
202教室 主楼二楼 10 在用



请问我应该怎么样得到下面信息呢：

如果教室较多，考虑动态语句的话应怎么实现呢：


姓名 语文 地点1 座位1 状态1 数学 地点2 座位2 状态2 物理 地点3 座位3 状态3
---- ---- ---- ----
李四 101教室 教西一楼 22 在用 202教室 主楼二楼 10 在用 101教室 教西一楼 22 在用
张三 202教室 主楼二楼 10 在用 101教室 教西一楼 22 在用 202教室 主楼二楼 10 在用
-------------------

if object_id('[tb]') is not null drop table [tb]
create table tb(姓名 varchar(10) , 课程 varchar(10) ,教室 varchar(20))
insert into tb values('张三' , '语文' , '101教室')
insert into tb values('张三' , '数学' , '202教室')
insert into tb values('张三' , '物理' , '101教室')
insert into tb values('李四' , '语文' , '202教室')
insert into tb values('李四' , '数学' , '101教室')
insert into tb values('李四' , '物理' , '202教室')

if object_id('[kc]') is not null drop table [kc]
create table kc(教室 varchar(20) , 地点 varchar(20) ,座位 varchar(20),状态 varchar(20))
insert into kc values('101教室' , '教西一楼' , '22', '在用')
insert into kc values('202教室' , '主楼二楼' , '10', '在用')

go

[dawugui]

if object_id('[tb]') is not null drop table [tb]
create table tb(姓名 varchar(10) , 课程 varchar(10) ,教室 varchar(20))
insert into tb values('张三' , '语文' , '101教室')
insert into tb values('张三' , '数学' , '202教室')
insert into tb values('张三' , '物理' , '101教室')
insert into tb values('李四' , '语文' , '202教室')
insert into tb values('李四' , '数学' , '101教室')
insert into tb values('李四' , '物理' , '202教室')
if object_id('[kc]') is not null drop table [kc]
create table kc(教室 varchar(20) , 地点 varchar(20) ,座位 varchar(20),状态 varchar(20))
insert into kc values('101教室' , '教西一楼' , '22', '在用')
insert into kc values('202教室' , '主楼二楼' , '10', '在用')
go

declare @sql varchar(8000)
set @sql = 'select m.姓名 '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then m.课程 else '' '' end) [课程' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then n.地点 else '' '' end) [地点' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then n.座位 else '' '' end) [座位' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then n.状态 else '' '' end) [状态' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select count(1) from tb where 姓名 = t.姓名 and 课程 < t.课程) + 1 from tb t) s) as a
set @sql = @sql + ' from (select t.* , px = (select count(1) from tb where 姓名 = t.姓名 and 课程 < t.课程) + 1 from tb t) m , kc n where m.教室 = n.教室 group by 姓名'
exec(@sql) 

drop table tb , kc

/*
姓名         课程1        地点1                  座位1                  状态1                  课程2        地点2                  座位2                  状态2                  课程3        地点3                  座位3                  状态3                  
---------- ---------- -------------------- -------------------- -------------------- ---------- -------------------- -------------------- -------------------- ---------- -------------------- -------------------- -------------------- 
李四         数学         教西一楼                 22                   在用                   物理         主楼二楼                 10                   在用                   语文         主楼二楼                 10                   在用
张三         数学         主楼二楼                 10                   在用                   物理         教西一楼                 22                   在用                   语文         教西一楼                 22                   在用

*/