sql合并组合列，并过滤掉相同的

今天又在论坛发现了一个问题，思考了半天，原来并没有那么麻烦。工作中可能会经常用到。总结分享给大家；

问题是这样的：
原表：
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
就是合并组合,并过滤相同的

思路就是，先按照OrderSn 将列NAME组合；再将列name进行过滤
在过滤的时候，可以考虑先将name行转列，再进行列转行，列转行的时候取distinct就可以了

代码实现如下：

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<[e-mail])
begin
set @sql=(select name from #stage2 where [e-mail])
update #stage2 set name=(select stuff ((select distinct ','+ value from string_split(@sql,',') for xml path('')),1,1,'')) where [e-mail]
set @[e-mail]+1
end

实现结果：

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

补充：方法2；使用cross apply简化

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

 

论坛问题链接