利用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

转自：http://blog.****.net/fox123871/article/details/6656334