关于行合并数据

[weixin_43998691]

我想合亲一个表的数据
ID OrderSn NAME
1 11111 111,112,113
2 11111 111,114,115
3 11111 111,114,116
4 11112 221,222,223
5 11112 221,222,225

我想把这些记录生成二条记录.结果如下
OrderSn Name
11111 111,112,113,114,115
11112 221,222,223,225
就是合并组合,并过滤相同的
谢谢各位

[向阳的花儿]

create table weixin(ordersn varchar(20),name varchar(200))
insert into weixin values('11111','111,112,113'),
('11111','111,114,115'),
('11111',']111,114,116'),
('11112','221,222,223'),
('11112','221,222,225')
----------------------------以上为测试数据-----------------------------------------
--先合并
select * into #stage2 from (
select s.ordersn,stuff(( select ','+b.name from weixin as b where b.ordersn=s.ordersn for xml path('')),1,1,'') as name,
row_number() over(order by getdate()) as n
from weixin as s
group by ordersn)a
--合并后去重，去重使用行转列
declare @i int
declare @count int
declare @sql varchar(max)
set @count=(select count(1) from #stage2)
set @i=1
while(@i<=@count)
begin
set @sql=(select name from #stage2 where n=@i)
update #stage2 set name=(select stuff ((select distinct ','+ value from string_split(@sql,',') for xml path('')),1,1,'')) where n=@i
set @i=@i+1
end

[XXXXn帅气]

SQL Server中string_split函数

[向阳的花儿]

补充： 方法2

--使用cross apply将name直接行转列后筛掉重复
SELECT distinct ordersn,value as name into #tem01
FROM weixin t
    CROSS APPLY STRING_SPLIT(t.name, ',')v
--合并列               
select ordersn,(select stuff((select ','+b.name from #tem01 as b where b.ordersn=a.ordersn for xml path('')),1,1,'')) as name 
from #tem01 as a
	group by a.ordersn