SQL Server2005 使用FOR XML选项进行字符串的串联聚合

 

使用FOR XML查询选项的PATH模式，可以用于字符串的串联聚合，并且可以很方便地组织聚合后的文本描述。

先看看下面的节选：

 

with arg1 as
(
    select top 10 [lt_username],(
        select CAST(n2.room as nvarchar(128) )+':'+CAST(n2.point as nvarchar(8))+',' as [text()]
        from [newbbspoints] n2
        where n2.lt_username = n1.lt_username
        order by n2.point desc 
        for xml path('')
    ) as [Description]
    from dbo.[newbbspoints] as n1 
)
select [lt_username], stuff([Description], Len([Description]), 1, '') as [Description] from arg1

 

使用for xml path('')提供了一个空字符串作为输入，所以未产生包装器(wrapper)元素，
通过CAST(n2.room as nvarchar(128) )+':'+CAST(n2.point as nvarchar(8))+','
可以利用各列的数据来串联出此字段的描述。

其中使用stuff截取最后一个生成的分割符','

以下的例子利用一个已在存在表的数据去更新另外一下表，更新的字段内容为第一个表的两个字段的字符串串联。

if object_id('dbo.BBSPoints') is not null
    drop table dbo.BBSPoints
if object_id('dbo.CJB_Temp') is not null
    drop table dbo.CJB_Temp;    
create table     dbo.BBSPoints
(
    lt_username    nvarchar(20) not null,
    room    nvarchar(128) not null,
    point int
)
insert into dbo.BBSPoints(lt_username,room,point) values(N'zdg',N'社区支持',2734);
insert into dbo.BBSPoints(lt_username,room,point) values(N'zdg',N'扩充话题',2734);
insert into dbo.BBSPoints(lt_username,room,point) values(N'billok',N'产品/厂家',654);
insert into dbo.BBSPoints(lt_username,room,point) values(N'billok',N'MS-SQL Server',45);
insert into dbo.BBSPoints(lt_username,room,point) values(N'junbiaochen',N'C#',785);
insert into dbo.BBSPoints(lt_username,room,point) values(N'junbiaochen',N'asp.net',265);
create table dbo.CJB_Temp
(
    UserName    nvarchar(20) not null primary key,
    Title    nvarchar(256)
)
insert into dbo.CJB_Temp(UserName,Title) values(N'zdg',N'');
insert into dbo.CJB_Temp(UserName,Title) values(N'billok',N'');
insert into dbo.CJB_Temp(UserName,Title) values(N'junbiaochen',N'');

with arg1 as
(
    select [lt_username],(
        select CAST(n2.room as nvarchar(128) )+':'+CAST(n2.point as nvarchar(8))+',' as [text()]
        from dbo.[BBSPoints] n2
        where n2.lt_username = n1.lt_username
        order by n2.point desc 
        for xml path('')
    ) as [Description]
    from dbo.[BBSPoints] as n1
)
,arg2 as
(
    select [lt_username], stuff([Description], len([Description]), 1, '') as  [Description] from arg1
)
update t
    set [Title] = a2.[Description]
    from dbo.[CJB_Temp] t
        join arg2 a2 on a2.[lt_username] = t.[UserName]

select * from dbo.[CJB_Temp];

drop table dbo.[BBSPoints];
drop table dbo.[CJB_Temp];