dianhao s_name
1001 1,2,3,3,4,5,6,,,
1002 2,7,8,9,10,10
去掉重复和没有值的变成dianhao s_name
1001 1,2,3,4,5,6
1002 2,7,8,9,10
1001 1,2,3,3,4,5,6,,,
1002 2,7,8,9,10,10
去掉重复和没有值的变成dianhao s_name
1001 1,2,3,4,5,6
1002 2,7,8,9,10
insert into #tb
select 1001,'1,2,3,3,4,5,6'
union all select 1002,'2,7,8,9,10,10'
SELECT distinct dianhao,SUBSTRING([s_name],number,CHARINDEX(',',[s_name]+',',number)-number) as [s_name]
into test
from #tb a, master..spt_values
where number >=1 and type='p'
and number<len([s_name]) and substring(','+[s_name],number,1)=','
go
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[s_name]+',' from test where dianhao=@a
if len(@s)>0 set @s=left(@s,len(@s)-1)
return (@s)
end
select dianhao,dbo.fn_b(dianhao) as s_name
from test
group by dianhaodrop table #tb,test
drop function dbo.fn_b/*
dianhao s_name
-----------------------
1001 1,2,3,4,5
1002 10,2,7,8,9*/
create table #tb(dianhao int,s_name varchar(100))
insert into #tb
select 1001,'1,2,3,3,4,5,6,,,'
union all select 1002,'2,7,8,9,10,10';WITH a1 AS
(
SELECT a.dianhao,b.s_name,ROW_NUMBER() OVER (ORDER BY GETDATE()) re
FROM
(SELECT dianhao, s_name=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(s_name)),',','</v><v>')+'</v></root>') FROM #tb) a
OUTER APPLY
(SELECT s_name = C.v.value('.','NVARCHAR(MAX)') FROM a.s_name.nodes('/root/v') C(v)) b
WHERE b.s_name<>''
)
,a2 AS
(
SELECT dianhao,s_name,MIN(re) re
FROM a1
GROUP BY dianhao,s_name
)
SELECT dianhao,s_name=STUFF((select ','+s_name FROM a2 WHERE dianhao=a.dianhao ORDER BY re FOR XML PATH('')),1,1,'')
FROM a2 a
GROUP BY dianhao