数据比较合并

[Weaseatiti]

tb1
name
name1,name2,name4


tbl2
name
name1,name3,name5

求
name
name1,name2,name3,name4,name5

[RINK_1]

if OBJECT_ID(N'tempdb..#t1') is not null
drop table #t1
go

create table #t1(name varchar(1000))

insert into #t1
select 'name1,name2,name4'


if OBJECT_ID(N'tempdb..#t2') is not null
drop table #t2
go

create table #t2(name varchar(1000))

insert into #t2
select 'name1,name3,name5'

with cte_1
as
(select SUBSTRING(A.name,number,CHARINDEX(',',A.name+',',number)-number) as single_name,
 row_number() over (order by number) as seq
 from #t1 A
 join master.dbo.spt_values B on CHARINDEX(',',','+A.name,number)=number
 where type='p'),
 
cte_2
as
(select SUBSTRING(A.name,number,CHARINDEX(',',A.name+',',number)-number) as single_name,
 row_number() over (order by number) as seq
 from #t2 A
 join master.dbo.spt_values B on CHARINDEX(',',','+A.name,number)=number
 where type='p'),
 
cte_3
as
(select single_name,case when seq>1 then 2*(seq-1) else seq end as seq from cte_1
 union 
 select single_name,case when seq>1 then 2*seq-1 else seq end as seq from cte_2)

select stuff((select ','+single_name from cte_3 order by seq for xml path('')),1,1,'') as name

[二月十六]

CREATE FUNCTION dbo.F_Split
 (
     @SplitString nvarchar(max),  --源字符串
     @Separator nvarchar(10)=' '  --分隔符号，默认为空格
 )
 RETURNS @SplitStringsTable TABLE  --输出的数据表
 (
     [id] int identity(1,1),
     [value] nvarchar(max)
 )
 AS
 BEGIN
     DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     DECLARE @ReturnText nvarchar(max);

     SELECT @CurrentIndex=1;
     WHILE(@CurrentIndex<=len(@SplitString))
         BEGIN
             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
             IF(@NextIndex=0 OR @NextIndex IS NULL)
                 SELECT @NextIndex=len(@SplitString)+1;
                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
                 SELECT @CurrentIndex=@NextIndex+1;
             END
     RETURN;
 END
GO

--测试数据
if not object_id(N'Tempdb..#tb1') is null
	drop table #tb1
Go
Create table #tb1([name] nvarchar(37))
Insert #tb1
select N'name1,name2,name4'
GO
if not object_id(N'Tempdb..#tb2') is null
	drop table #tb2
Go
Create table #tb2([name] nvarchar(37))
Insert #tb2
select N'name1,name3,name5'
Go
--测试数据结束
SELECT STUFF(
       (
           SELECT ',' + t1.value + ',' + t2.value
           FROM
           (
               SELECT t.*
               FROM #tb1
                   CROSS APPLY
               (SELECT * FROM dbo.F_Split(name, ',') ) t
           ) t1
               JOIN
               (
                   SELECT t.*
                   FROM #tb2
                       CROSS APPLY
                   (SELECT * FROM dbo.F_Split(name, ',') ) t
               ) t2
                   ON t2.id = t1.id
           FOR XML PATH('')
       ),
       1,
       7,
       ''
            ) AS NAME;