Sample code for merging group information using XML FOR PATH (picture and text)-XML/RSS Tutorial-php.cn

Sample code for merging group information using XML FOR PATH (picture and text)

黄舟

Release： 2017-03-20 16:35:41

Original

1729 people have browsed it

Recently, when doing statistical functions, I often need to use the mergegrouping content. If I only use the aggregation function based on the grouped statistical values, that’s it. , if we process the grouped string columns, we must write our own function to process it. For example, if there is such data information: To group the above data and obtain statistical results:

Method of generating XML
using SQL technology It’s so troublesome to handle functions.

Extension: For XML Path
Because the SELECT clause does not specify any column name aliases, the resulting subelement names are the same as the corresponding column names in the SELECT clause. If no information is specified for path, a <
row
> tag will be added for each row in the rowset.

SQL statement:

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

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

select DepartmentName,dbo.Sum_ByGroup(DepartmentName) as EmployeesList from Employees
group by DepartmentName
go

Copy after login

Overrides the default <

row

>. For example, the following query will return the corresponding <

Employee

> element for each row in the rowset.

SQL statement:

select
 DepartmentName,
stuff
((
select
 
&#39;
,
&#39;
+
EmpoyeeName 
from
 Employees 
where
 DepartmentName 
=
 e.DepartmentName 
for
 xml path(
&#39;&#39;
)),
1
,
1
,
&#39;&#39;
) 
as
 EmployeesList 
from
 Employees E
group
 
by
 DepartmentName

Copy after login

## # 3. If a zero-length string is specified, no wrapping elements will be generated.
Result:

4. You can add a single top-level element by specifying the
root
option in the FOR XML SQL statement:

select
 DepartmentName,(
select
 
&#39;&#39;
+
EmpoyeeName 
from
 Employees 
where
 DepartmentName 
=
 e.DepartmentName 
for
 xml path) 
as
 EmployeesList 
from
 Employees E
group
 
by
 DepartmentName

Copy after login

## Result:

The above is the detailed content of Sample code for merging group information using XML FOR PATH (picture and text). For more information, please follow other related articles on the PHP Chinese website!