SQL同一列同一行中的数据为下面:
5000000009;5000000009;5000000010;5000000011
此为字段OrderNo的值
如何把相同的值合并:
即合并结果后为
5000000009;5000000010;5000000011
5000000009;5000000009;5000000010;5000000011
此为字段OrderNo的值
如何把相同的值合并:
即合并结果后为
5000000009;5000000010;5000000011
--借用下小F的
if object_id('f_split')is not null drop function f_split
go
create function f_split
(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)returns varchar(8000)
as
begin
declare @retval varchar(max)
select @retval=(select distinct col+@split from (
select substring(@s,number,charindex(@split,@s+@split,number)-number)as col
from master..spt_values
where type='P' and number<=len(@s+'a')
and charindex(@split,@split+@s,number)=number
)t for xml path(''));
return left(@retval,len(@retval)-1);
end
go
--测试
select dbo.f_split('5000000009;5000000009;5000000010;5000000011',';')
--结果
/*
5000000009;5000000010;5000000011(1 行受影响)
*/
go
create function fn_filter
(
@str varchar(100),
@split varchar(1)
)
returns varchar(100)
as
begin
declare @ret varchar(100)
declare @xml xml
set @xml='<x>'+replace(@str,@split,'</x><x>')+'</x>'
select @ret=stuff((select @split+col
from (select v.value('.','varchar(100)') col
from @xml.nodes('/x') n(v))t
group by col for xml path('')),1,1,'')
return @ret
endselect dbo.fn_filter('5000000009;5000000009;5000000010;5000000011',';')
/*
5000000009;5000000010;5000000011
*/
go
insert into tb select 1,'5000000009;5000000009;5000000010;5000000011'
go
with c1 as(
select id,left(col+';',charindex(';',col+';')-1) as col,right(col+';',len(col+';')-charindex(';',col+';')) as c2 from tb
union all
select id,left(c2,charindex(';',c2)-1) as col,right(c2,len(c2)-charindex(';',c2)) as c2 from c1 where charindex(';',c2)>0
),c2 as(
select distinct id,col from c1
)select
a.id,Col=stuff(b.Col.value('/R[1]','nvarchar(max)'),1,1,'')
from
(select distinct id from c2) a
Cross apply
(select COl=(select N','+Col from c2 where id=a.id For XML PATH(''), ROOT('R'), TYPE))bgo
drop table tb
/*
id Col
----------- -------------------------------------------
1 5000000009,5000000010,5000000011(1 行受影响)*/