select a.*, d.v1,d.v2,d.v3
from TableA a
join 
(
	select 'TableSAP' as t, tsid id, tsVal1 v1, tsVal2 v2, tsVal3 v3 from TableSAP
	union all
	select 'TableDAP', * from TableDAP
	union all
	select 'TableOPT', * from TableOPT
) d on a.ipType = d.t and a.ipID = d.id

declare @TableA table
(
	TAID	int, 
	IPType	varchar(10), 
	IPID	int
)
insert into @TableA
select	1, 'TableSAP', 1	union all
select	2, 'TableDAP', 1	union all
select	3, 'TableOPT', 5	union all
select	4, 'TableSAP', 4

declare @TableSAP table
(
	TSID 	int,
	tsVal1	varchar(10),
	tsVal2	varchar(10),
	tsVal3	varchar(10)
)
insert into @TableSAP
select	1, 'green',  'red',     'blues'	union all
select	2, 'pink',   'yellow',  'white'	union all
select	4, 'orange', 'magneta', 'black'

declare @TableDAP table
(
	TDID 	int,
	tdVal1 	varchar(10),
	tdVal2 	varchar(10),
	tdVal3	varchar(10)
)
insert into @TableDAP
select	1, 'car',   'boat', 'bike'	union all
select	2, 'skate', 'shoe', 'balloon'

declare @TableOPT table
(
	TOID 	int,
	toVal1 	varchar(10),
	toVal2 	varchar(10),
	toVal3	varchar(10)
)
insert into @TableOPT
select	1, 'cherry', 'banana', 'apple'	union all
select	5, 'kiwi',   'grape',  'melon'

select	a.TAID,
	IPType 	= right(a.IPType, 3),
	IPID	= a.IPID,
	Value1	= case 	IPType
			when 'TableSAP' then sap.tsVal1
			when 'TableDAP' then dap.tdVal1
			when 'TableOPT' then opt.toVal1
			end,
	Value2	= case 	IPType
			when 'TableSAP' then sap.tsVal2
			when 'TableDAP' then dap.tdVal2
			when 'TableOPT' then opt.toVal2
			end,
	Value3	= case 	IPType
			when 'TableSAP' then sap.tsVal3
			when 'TableDAP' then dap.tdVal3
			when 'TableOPT' then opt.toVal3
			end
from	@TableA a 
	left join @TableSAP sap
	on	a.IPType	= 'TableSAP'
	and	a.IPID		= sap.TSID
	left join @TableDAP dap
	on	a.IPType	= 'TableDAP'
	and	a.IPID		= dap.TDID
	left join @TableOPT opt
	on	a.IPType	= 'TableOPT'
	and	a.IPID		= opt.TOID

/*
TAID        IPType IPID        Value1     Value2     Value3     
----------- ------ ----------- ---------- ---------- ---------- 
1           SAP    1           green      red        blues
2           DAP    1           car        boat       bike
3           OPT    5           kiwi       grape      melon
4           SAP    4           orange     magneta    black
*/