SQL2005 一条语句搞定字符串分割后合并

表1：

Create table tb1 (col1 int,col2 varchar(50))
insert into tb1 select 1,'1,2'
insert into tb1 select 2,'1,2,3'

表2：

create table tb2 (col1 int,col2 varchar(50))
insert into tb2 select 1,'t1'
insert into tb2 select 2,'t2'
insert into tb2 select 3,'t3'

表1的col2对应表2的col1

select col1,stuff((select ','+rtrim(col2)
from
(select aa.col1,bb.col2 from (
     select a.COl1,b.Col2 from
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tb1)a
       outer apply
     (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b ) aa join tb2 bb on aa.col1=bb.col1
)b
                 where b.col1=a.col1 for xml path('')
                ),1,1,'') as [col2]
from (select distinct col1 from
(select aa.col1,bb.col2 from (
select
    a.COl1,b.Col2
from
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tb1)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
) aa join tb2 bb on aa.col1=bb.col1
) b
) a

最后输出结果：

col1        col2
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           t1,t1
2           t2,t2,t2

(2 行受影响)

sql2000用函数处理：

select dbo.fun_get_SplitStr(col,',') from tb1

--SQL分割字符串函数
alter FUNCTION dbo.fun_get_SplitStr(
@SourceSql    varchar(8000),
@StrSeprate    varchar(100))
returns varchar(8000)

AS    
BEGIN  
declare @ch as varchar(100)  
declare @Sql    varchar(8000)
set @Sql=''
set     @SourceSql =@SourceSql+@StrSeprate    
while(@SourceSql<>'')  
BEGIN
        set      @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1)
        select @Sql=@Sql+','+col2 from tb2 where col1=@ch  
   --insert   @temp   values(@ch)  
   set      @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')  
END  
return   @Sql
END