declare @t1 table(col1 varchar(4),col2 varchar(4)) insert into @t1 select 'A','a' insert into @t1 select 'A','b' insert into @t1 select 'B','b' insert into @t1 select 'C','a' insert into @t1 select 'C','c' insert into @t1 select 'D','e' insert into @t1 select 'E','g'declare @t2 table(col1 varchar(4),col2 varchar(4))declare @col2 varchar(4) set @col2='a'insert into @t2 select * from @t1 where col2=@col2while @@rowcount<>0 begin insert into @t2 select a.* from @t1 a,@t2 b where (a.col1=b.col1 or a.col2=b.col2) and not exists(select 1 from @t2 where col1=a.col1 and col2=a.col2) enddeclare @str1 varchar(4000),@str2 varchar(4000) select @str1=isnull(@str1,'')+';'+col1 from @t2 group by col1 select @str2=isnull(@str2,'')+';'+col2 from @t2 group by col2select stuff(@str1,1,1,''),stuff(@str2,1,1,'')
insert into @t1 select 'A','a'
insert into @t1 select 'A','b'
insert into @t1 select 'B','b'
insert into @t1 select 'C','a'
insert into @t1 select 'C','c'
insert into @t1 select 'D','e'
insert into @t1 select 'E','g'declare @t2 table(col1 varchar(4),col2 varchar(4))declare @col2 varchar(4)
set @col2='a'insert into @t2 select * from @t1 where col2=@col2while @@rowcount<>0
begin
insert into @t2
select
a.*
from
@t1 a,@t2 b
where
(a.col1=b.col1 or a.col2=b.col2)
and
not exists(select 1 from @t2 where col1=a.col1 and col2=a.col2)
enddeclare @str1 varchar(4000),@str2 varchar(4000)
select @str1=isnull(@str1,'')+';'+col1 from @t2 group by col1
select @str2=isnull(@str2,'')+';'+col2 from @t2 group by col2select stuff(@str1,1,1,''),stuff(@str2,1,1,'')