去重复
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
*/
