Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
0


create table Table_1(Sr_no number, Item_1_id number, Item_2_id number);
create table Table_2(Item_id number, Item_Name varchar2(21));
insert all
into Table_1 values (1 ,3 ,2) 
into Table_1 values (2 ,2 ,1) 
into Table_1 values (3 ,1 ,3)
select * from dual;
3 rows affected

insert all 
into Table_2  values (1, 'Key Board') 
into Table_2  values (2, 'Monitor') 
into Table_2  values (3, 'CPU')
select * from dual;
3 rows affected

select t1.Sr_no,
case when t1.Item_1_id = t2.Item_id then t2.Item_Name end item_1_name,
case when t1.Item_1_id = t3.Item_id then t3.Item_Name end item_2_name
from Table_1 t1 left join Table_2 t2
on t1.Item_1_id = t2.item_id
left join Table_2 t3
on t1.Item_1_id = t3.item_id


SR_NO
ITEM_1_NAME
ITEM_2_NAME


3
Key Board
Key Board

2
Monitor
Monitor

1
CPU
CPU


select t1.Sr_no,
(select item_name from table_2 t2 where t2.item_id = t1.Item_1_id) Item_1_Name,
(select item_name from table_2 t2 where t2.item_id = t1.Item_2_id) Item_2_Name
from Table_1 t1 


SR_NO
ITEM_1_NAME
ITEM_2_NAME


1
CPU
Monitor

2
Monitor
Key Board

3
Key Board
CPU


select r1.sr_no,r1.item_name,r2.item_name from 
(select t1.sr_no, t2.item_name
from table_1 t1, table_2 t2
where t1.item_1_id = t2.item_id) r1,
(select t1.sr_no, t2.item_name
from table_1 t1, table_2 t2
where t1.item_1_id = t2.item_id) r2
where r1.sr_no = r2.sr_no
order by r1.sr_no


SR_NO
ITEM_NAME
ITEM_NAME


1
CPU
CPU

2
Monitor
Monitor

3
Key Board
Key Board