先有一表Share_BuildingAndFloor
表中有2个字段
Building Floor
11        11
12        23
43        23

现在要求得到以下结果集
Building         Floor
11,12,43       11,23,23

做法如下:
declare @basic table
(
 Building nvarchar(100),    --幢数
 [Floor] nvarchar(100),    --层次
)

--得到幢数
declare   @Building   varchar(8000)
set   @Building=''
select @Building=@Building + cast(Building as nvarchar) + ',' from share_buildingandfloor
where SerialNumberID = @SerialNumberID

update @basic set Building =
left(@Building,len(@Building)-1)

--得到层次
declare   @Floor   varchar(8000)
set   @Floor=''
select @Floor=@Floor + cast([Floor] as nvarchar) + ',' from share_buildingandfloor
where SerialNumberID = @SerialNumberID

update @basic set [Floor] =
left(@Floor,len(@Floor)-1)


select * from @basic

输出结果集:
Building         Floor
11,12,43       11,23,23