利用XML FOR PATH 合并分组信息

最新推荐文章于 2026-03-24 12:26:08 发布

原创最新推荐文章于 2026-03-24 12:26:08 发布 · 3.2k 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

MSSQL数据库

本文展示了如何使用SQL Server的T-SQL技术简化数据汇总过程，通过创建函数和利用ForXMLPath特性，实现了高效的数据分组与汇总。文章详细介绍了SQL语句的实现方式，包括直接生成XML格式的汇总数据，以及如何通过ForXMLPath参数生成不同形式的XML输出。

-- ================================================
-- 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

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

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

结果：