去重复


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
[解决办法]

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'


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 dianhao

drop table #tb,test
drop function dbo.fn_b

/*
dianhao     s_name
-----------------------
10011,2,3,4,5
100210,2,7,8,9

*/