select R.Name,Type_tel.Type_name,Tel.PhoneNo From R join ...

create table #fio (fio varchar(100), phoneid int)
create table #phone (phoneid int, phonetype int)

insert #phone select 1, 1
insert #phone select 2, 2
insert #fio select 'вася', 1
insert #fio select 'вася', 2
insert #fio select 'петя', 1


select
	fio, max( h1.phoneid), max( h2.phoneid) 
from 
	#fio  f
	left join #phone h1 on h1.phoneid = f.phoneid and h1.phonetype = 1
	left join #phone h2 on h2.phoneid = f.phoneid and h2.phonetype = 2
group by 
	fio

declare @fio table(name varchar(100), idPhone int);
insert into @fio values ('Ivanov', 1), ('Ivanov', 2), ('Ivanov', 3), ('Petrov', 4);

declare @ph_types table (idTypePhone int, NameTypePhone varchar(100));
insert into @ph_types values (1, 'simple phone'), (2,'super phone'), (3,'super-puper phone')

declare @phones table (idPhone int, num bigint, idTypePhone int);
insert into @phones values (1, 222, 1), (2, 333, 2), (3, 067123456788, 3), (4, 111111111, 1);

select f.name, p.num, t.NameTypePhone
from @fio f join @phones p on f.idPhone = p.idPhone 
            join @ph_types t on p.idTypePhone = t.idTypePhone;

insert #phone select 1, 1
insert #phone select 2, 2
insert #phone select 3, 1
insert #phone select 4, 2
insert #fio select 'вася', 1
insert #fio select 'вася', 2
insert #fio select 'вася', 3
insert #fio select 'вася', 4

declare @h table (h_id int primary key, h_Name varchar(30));
declare @hp table (h_id int, phone varchar(30), phone_type int not null, primary key (h_id, phone));

insert into @h
values
 (1, 'Иванов И.И.');

insert into @hp
values
 (1, '111', 1),
 (1, '222', 1),
 (1, '333', 2),
 (1, '444', 2);

select
 h.*,
 stuff(p.x.query('for $p in (record[phone_type = 1]/phone) return concat(", ", string($p))').value('.', 'varchar(max)'), 1, 2, '') as [phone_type = 1],
 stuff(p.x.query('for $p in (record[phone_type = 2]/phone) return concat(", ", string($p))').value('.', 'varchar(max)'), 1, 2, '') as [phone_type = 2]
from
 @h h outer apply
 (select (select phone, phone_type from @hp where h_id = h.h_id for xml path('record'), type)) p(x);