MSSQL 字段分组拼接

方法1：缺点，不去重，不去空；见表1

with t  as(
  select 'A' parent, 'A1' child union all
  select 'A', 'A1' union all
  select 'A', '' union all
  select 'A', null union all
  select 'A', 'A2' union all
  select 'B', 'B1' union all
  select 'B', 'B2' union all
  select 'C', 'C1' union all
  select 'C', 'C2' 
)
SELECT parent, 
STUFF(
     ( 
      SELECT ','+ child FROM t a WHERE b.parent = a.parent FOR XML PATH('')
     ),1 ,1, '') children 
FROM t b 
GROUP BY parent

 

表1：

 

 方法2：通过游标来处理，去重，去空。见表2

--定义游标并进行合并处理  
declare @t table(parent varchar(100),child varchar(100))--定义结果集表变量 
declare my_cursor cursor local for  
with t1  as(
  select 'A' parent, 'A1' child union all
  select 'A', 'A1' union all
  select 'A', '' union all
  select 'A', null union all
  select 'A', 'A2' union all
  select 'B', 'B1' union all
  select 'B', 'B2' union all
  select 'C', 'C1' union all
  select 'C', 'C2' 
) 
select parent, child from t1 order by parent, child
declare @parent_old varchar(100) , @parent varchar(100) , @child varchar(100), @s varchar(100)  
open my_cursor  
fetch my_cursor into @parent , @child  
select @parent_old = @parent , @s=''  
while @@FETCH_STATUS = 0  
begin  
    if @parent = @parent_old 
    begin 
      if charindex(@child, @s)=0 and @child<>''
        select @s = @s + ',' + cast(@child as varchar)  
    end else  
      begin  
        insert @t values(@parent_old , stuff(@s,1,1,''))  
        select @s = ',' + cast(@child as varchar) , @parent_old = @parent  
      end  
    fetch my_cursor into @parent , @child  
END  
insert @t values(@parent_old , stuff(@s,1,1,''))  
close my_cursor  
deallocate my_cursor  
select * from @t

表2：

 

转载于:https://www.cnblogs.com/adsoft/p/11474951.html