表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