几种多行变一行

 --生成测试数据 
create table T(department int,person varchar(20)) 
insert into T select 1,'张三' 
insert into T select 1,'李四' 
insert into T select 1,'王五' 
insert into T select 2,'赵六' 
insert into T select 2,'邓七' 
insert into T select 2,'刘八' 
insert into T select 2,'刘九' 
insert into T select 3,'刘十' 
insert into T select 3,'周十一' 
 
 
 
go 
 
select  *  from  t 
 
--1.用户定义函数 
create function f_str ( @department int ) 
returns varchar(8000) 
as  
    begin 
        declare @ret varchar(8000) 
        set @ret = '' 
        select  @ret = @ret + ',' + person 
        from    T 
        where   department = @department 
        set @ret = stuff(@ret, 1, 1, '') 
        return @ret  
    end 
go 
 
 
--执行 
select  distinct 
        department , 
        person = dbo.f_str(department) 
from    T 
go 
 
 
/* 
department    person 
1    张三,李四,王五 
2    赵六,邓七,刘八 
*/ 
 
 
 
--2.XML FOR PATH 
select  B.department , 
        left(NT, len(NT) - 1) as paths 
from    ( select    department , 
                    ( select    person + ',' 
                      from      T 
                      where     department = A.department 
                      order by  department 
                    for 
                      xml path('') 
                    ) as NT 
          from      T A 
          group by  department 
        ) B 
         
  
/* 
department    paths 
1    张三,李四,王五 
2    赵六,邓七,刘八 
*/ 
 
  
 
--改一下上面的写法 
select  department , 
        person = stuff(( select ',' + person 
                         from   T t 
                         where  department = NT.department 
                       for 
                         xml path('') 
                       ), 1, 1, '') 
from    T nt 
group by department  
 
 
------------------------------------ 
--3.cte 
; 
with    result 
          as ( select   department , 
                        person , 
                        row = row_number() over ( partition by department order by department ) 
               from     t 
             ), 
        result2 
          as ( select   department , 
                        cast(person as nvarchar(100)) person , 
                        row 
               from     result 
               where    row = 1 
               union all 
               select   a.department , 
                        cast(b.person + ',' + a.person as nvarchar(100)) , 
                        a.row 
               from     result a 
                        join result2 b on a.department = b.department 
                                       and a.row = b.row + 1 
             ) 
              
            
              
    select  department , 
            person 
    from    result2 a 
    where   row = ( select  max(row) 
                    from    result 
                    where   department = a.department 
                  ) 
    order by department 
option  ( maxrecursion 0 ) 
 
 
-------------------------------  
 
  
 
--删除测试数据 
drop function f_str 
drop table T