SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test(id int, display_name varchar(50))
insert into test values (1, 'Name Surname')
1 rows affected

insert into test values (2, 'Name Surname2')
1 rows affected

create table test2(id int, firstname varchar(50),lastname varchar(50))
insert into test2 values (1, 'Name', 'Surname')
1 rows affected

insert into test2 values (2, 'Name2', 'Surname')
1 rows affected

SELECT a.*, b.*
FROM test a
join (select t2.*
             , CONCAT(t2.FirstName, ' ', t2.lastname) AS Concatenate
      from test2 t2) b on b.Concatenate = a.[display_name]



id
display_name
id
firstname
lastname
Concatenate


1
Name Surname
1
Name
Surname
Name Surname