SQL Server 2017

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

create table t(Name1 varchar(50),Name2 varchar(50),
Department1 varchar(50), Department2 varchar(50),
Location1 varchar(50), Location2 varchar(50))
insert into t values('Jhon',     'Alex',    
'IT',           'Marketing',      'London',      'Seattle'),
(
'Mark',    'Dan',      'Sales',          'R&D',            'Paris',       'Tokyo'
)
2 rows affected

select name,department,location
from t
cross apply
(values(name1,department1,location1),(name2,department2,location2)
)cc (name, department,location)


name
department
location


Jhon
IT
London

Alex
Marketing
Seattle

Mark
Sales
Paris

Dan
R&D
Tokyo