Home > Article > Backend Development > Sample code for merging group information using XML FOR PATH (picture and text)
Recently, when doing statistical functions, I often need to use the mergegrouping content. If I only use the aggregationfunction 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 91be08e08559183ef9a2877d6983a621 tag will be added for each row in the rowset.
-- ================================================ -- 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
select DepartmentName, stuff (( select ' , ' + EmpoyeeName from Employees where DepartmentName = e.DepartmentName for xml path( '' )), 1 , 1 , '' ) as EmployeesList from Employees E group by DepartmentName
4. You can add a single top-level element by specifying the
root
option in the FOR XML SQL statement:
select DepartmentName,( select '' + EmpoyeeName from Employees where DepartmentName = e.DepartmentName for xml path) as EmployeesList from Employees E group by DepartmentName
## 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!