By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tableA
(
ColumnA varchar(max) not null
)
insert into tableA values ('Apple, Ball, Chair, Doll, Egg, Fan, Gun, Hat, Ink, Jug')
1 rows affected
create table tableB
(
ColumnB varchar(max) not null
)
insert into tableB values ('Chair, Ball, Egg, Ink');
1 rows affected
select 1
| (No column name) |
|---|
| 1 |
with cte as
(
select
left(ColumnA, charindex(',', ColumnA + ',') - 1) as col1 ,
stuff(ColumnA, 1, charindex(',', ColumnA + ','), '') as col2
from tableA
union all
select
left(col2, charindex(',', col2 + ',') - 1),
stuff(col2, 1, charindex(',', col2 + ','), '')
from cte
where
col2 != ''
), cte2 as
(
select
left(ColumnB, charindex(',', ColumnB + ',') - 1) as col1 ,
stuff(ColumnB, 1, charindex(',', ColumnB + ','), '') as col2
from tableB
union all
select
left(col2, charindex(',', col2 + ',') - 1),
stuff(col2, 1, charindex(',', col2 + ','), '')
from cte2
where
col2 != ''
)
select * from cte where rtrim(ltrim(col1)) in (select rtrim(ltrim(col1)) from cte2)
option (maxrecursion 0);
| col1 | col2 |
|---|---|
| Ball | Chair, Doll, Egg, Fan, Gun, Hat, Ink, Jug |
| Chair | Doll, Egg, Fan, Gun, Hat, Ink, Jug |
| Egg | Fan, Gun, Hat, Ink, Jug |
| Ink | Jug |