set nocount on
create table #t1 (auto_card int, full_name varchar( 50 ))
insert into #t1 Values( 1111 ,'Иванов Иван Иванович')
insert into #t1 Values( 2222 ,'Петров Петр Петрович')
insert into #t1 Values( 3333 ,'Сидоров Сидор Сидорович')
create table #t2 (auto_card int, full_name varchar( 50 ),rel_id int,date_birth datetime)
insert into #t2 Values( 1111 ,'Иванова Елена Ивановна', 4 ,'1999-01-01')
insert into #t2 Values( 1111 ,'Иванова Марина Ивановна', 4 ,'2001-01-01')
insert into #t2 Values( 1111 ,'Иванова Светлана Сергеевна', 2 ,'1975-03-19')
insert into #t2 Values( 2222 ,'Петров Максим Сидорович', 3 ,'2001-09-01')
create table #t3 (rel_id int, name varchar( 50 ))
insert into #t3 Values( 1 ,'муж')
insert into #t3 Values( 2 ,'жена')
insert into #t3 Values( 3 ,'сын')
insert into #t3 Values( 4 ,'дочь')
create table #rep(id int identity( 1 , 1 ), auto_card int,relat varchar( 7000 ) default(''))
declare @str varchar( 8000 ),@id int
insert  #rep  (auto_card) select t.auto_card from #t1 t
select @id=(select max(id) from #rep)
while @id> 0 
begin
select @str=''
select @str=@str+ t3.name + ' '+t2.full_name + ' год.рожд '+cast(datepart(yyyy,t2.date_birth) as varchar( 4 ))+',' 
from #t2 t2 join #t3 t3 on t2.rel_id=t3.rel_id where t2.auto_card=(select auto_card from #rep where id=@id)
if len(@str)> 2  select @str=left(@str,len(@str)- 1 )
update #rep set relat=@str
where id=@id
select @id=@id- 1 
end
select t.*,r.relat from #t1 t join #rep r on t.auto_card=r.auto_card 
drop table #t1 drop table #t2 drop table #t3 drop table #rep