利用XML FOR PATH合并分组信息的示例代码（图文）

109 阅读 0 评论 72 点赞

我是靠谱客的博主跳跃大侠，最近开发中收集的这篇文章主要介绍利用XML FOR PATH合并分组信息的示例代码（图文），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

最近做统计功能的时候经常需要用到合并分组内容，如果只根据分组统计数值用聚合函数就可以了，如果处理分组后的字符串列呢，我们就必须要自己写函数处理了，比如有这样的数据信息：

现在要对上面数据进行分组，获取统计结果：

实现方式一：

-- ================================================
-- Description:合并分组内容
-- Author:夏保华
-- Date:2009-08-06
-- ================================================
create   table   Employees(DepartmentName varchar(50),EmpoyeeName  varchar(20))   
insert into Employees   
select '开发部','小刘' union all
select '开发部','小王' union all
select '开发部','小张' union all
select '工程部','老吴' union all
select '工程部','老李' union all
select '市场部','大兵' union all
select '市场部','大黄' union all
select '市场部','大虾' union all
select '市场部','大国'
go 

create function  Sum_ByGroup(@DepartmentName varchar(50))   
returns varchar(8000)   
as   
begin   
    declare @ret varchar(8000)   
    set   @ret  =  ''   
    select  @ret  =  @ret+','+EmpoyeeName from Employees where DepartmentName = @DepartmentName   
    set   @ret   =   stuff(@ret,1,1,'')   
    return   @ret     
end   
go

select DepartmentName,dbo.Sum_ByGroup(DepartmentName) as EmployeesList from Employees
group by DepartmentName
go
登录后复制

实现方式二：利用 T-SQL 技术生成 XML 的方法

select
 DepartmentName,
stuff
((
select
 
'
,
'
+
EmpoyeeName 
from
 Employees 
where
 DepartmentName 
=
 e.DepartmentName 
for
 xml path(
''
)),
1
,
1
,
''
) 
as
 EmployeesList 
from
 Employees E
group
 
by
 DepartmentName
登录后复制

呵呵，这样就是不是简单多了，不用再写一个函数去处理这么麻烦了。

扩展：For XML Path
1.在该 XML 中，生成的行集中的每个列值都包在元素中。由于 SELECT 子句未指定任何列名别名，因此生成的子元素名称与 SELECT 子句中相应的列名相同。如果未对path指定任何信息，针对行集中的每一行，将添加一个 <row> 标记。

SQL语句：

select
 DepartmentName,(
select
 
''
+
EmpoyeeName 
from
 Employees 
where
 DepartmentName 
=
 e.DepartmentName 
for
 xml path) 
as
 EmployeesList 
from
 Employees E
group
 
by
 DepartmentName
登录后复制

如：

2.您可以选择指定行元素名称，以覆盖默认的 <row>。例如，以下查询将针对行集中的每一行返回相应的 <Employee> 元素。

SQL语句：

select
 DepartmentName,(
select
 
''
+
EmpoyeeName 
from
 Employees 
where
 DepartmentName 
=
 e.DepartmentName 
for
 xml path(
'
Employee
'
)) 
as
 EmployeesList 
from
 Employees E
group
 
by
 DepartmentName
登录后复制

结果：

3.如果指定零长度字符串，则将不生成包装元素。
结果：

4.可以通过在 FOR XML 中指定 root 选项来添加单个顶级元素
SQL语句：

select
 DepartmentName,(
select
 
''
+
EmpoyeeName 
from
 Employees 
where
 DepartmentName 
=
 e.DepartmentName 
for
 xml path(
'
Employee
'
),root(
'
Root
'
)) 
as
 EmployeesList 
from
 Employees E
group
 
by
 DepartmentName
登录后复制

结果：