with t as
(
 select
  b.id, b.name_type, b.type,
  row_number() over (partition by b.id order by a.n) as rn
 from
  (values (1, 'Наименование'), (2, 'Раздел спец.'), (3, 'Марка мат.')) a(n, name_type) join
  [Таблица с атрибутами] b on b.name_type = a.name_type
)
select
 a.idPerent, a.id, a.product, a.version, t.name_type, t.type
from
 [Таблица с файлами] a left join
 t on t.id = a.id and t.rn = 1;

select
 a.idPerent, a.id, a.product, a.version,
 coalesce(b.name_type, c.name_type, d.name_type) as name_type,
 coalesce(b.type, c.type, d.type) as type
from
 [Таблица с файлами] a left join
 [Таблица с атрибутами] b on b.id = a.id and b.name_type = 'Наименование' left join
 [Таблица с атрибутами] c on b.id = a.id and c.name_type = 'Раздел спец.' left join
 [Таблица с атрибутами] d on b.id = a.id and d.name_type = 'Марка мат.';