create table #Location
(	LocationID	int,
	LocationName	varchar(30)
)

create table #Person
(
	PersonID	int,
	Name		varchar(30)
)

create table #Designation
(
	DesignationID	int,
	Description	varchar(30)
)

create table #LocationPerson
(
	LocationPersonID	int,
	LocationID		int,
	PersonID		int
)

create table #LocationPersonDesignation
(
	LocationPersonDesignationID	int,
	LocationPersonID		int,
	DesignationID			int
)

insert into #Location
select 1, '1001'	union all
select 2, '1002'

insert into #Person
select 1, 'ABC'	union all
select 2, 'XYZ'	union all
select 3, 'PQR'

insert into #Designation
select 1, 'Manager'	union all
select 2, 'Asst.Manager'

insert into #LocationPerson
select 1, 1, 1 union all
select 2, 2, 2 union all
select 3, 1, 3

insert into #LocationPersonDesignation
select	1, 1, 1	union all
select	2, 2, 1 union all
select  3, 3, 2

select 	l.LocationName,
	max(case when lpd.DesignationID = 1 then p.Name else '' end) as 'Manager',
	max(case when lpd.DesignationID = 2 then p.Name else '' end) as 'Asst. Manager'
from	#LocationPersonDesignation lpd inner join #LocationPerson lp
	on	lpd.LocationPersonID	= lp.LocationPersonID
	inner join #Location l
	on	lp.LocationID		= l.LocationID
	inner join #Person p
	on	lp.PersonID		= p.PersonID
group by l.LocationName

drop table #Location
drop table #Person
drop table #Designation
drop table #LocationPerson
drop table #LocationPersonDesignation