SQL Server 2005 中实现行连接的技巧

问题提出

酒店客房管理系统中，需要把某房间的内在住客人的姓名显示在房态图上。看似很简单，但其实在表（register）中每个客人的姓名是分别占一行的，这就需要把同一房号下姓名列的所有内容连接在一起组成一行。

register表结构及内容如下：

期望得到的结果如下：

 

分析及解决问题

显然，要想将同一房号下各行的姓名列连接在一起，必须建立一个虚拟表，并通过递归收录各行的姓名，将收录到的姓名追加到虚拟表的姓名列中，这是解决问题的关键。SQL2005里对With进行的加强，为实现我们的目标创造了条件。创建需要的虚拟表的方法如下：

with x( x_room_no,x_account_no,x_vc_name,cnt,length) --虚拟表的结构
as
(
select s_room_no,s_account_no,cast(vc_name as varchar(100)),count(*) over(partition by s_room_no),1  --虚拟表的数据来源
from register
union all
select x_room_no,s_account_no,cast(x_vc_name + ',' + r.vc_name as varchar(100)),x.cnt,x.length+1  --追加姓名列
from register r,x
where r.s_room_no=x_room_no and r.s_account_no>x_account_no --r.s_account_no>x_account_no是为是防止追加重复的行
)

值得注意的是虚拟表中用到的三个辅助列:x_account_no,cnt,length,其中,x_account_no列是为了确定当前已追加到哪一个账号了.cnt确定同一房号的个数,即客人个数.而length就表明当前已追加的客人个数.当cnt和length相等时,表明该行记录已加入了所以同房号的客人姓名.

有了符合要求的虚拟表,下面的一切都变得自然了:

select x_room_no as 房号,x_vc_name 住客列表
from x
where length = cnt
order by 1

不知朋友们对于这一问题还有不有更好的解决办法? 欢迎讨论!