应用XML FOR PATH兼并分组信息的示例代码（图文）【XML教程】,XML ，合并分组，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

效果：