Oracle 11g Release 2

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

create table my_item_lib (
    id_item number,
    lib_fr  varchar2(100),
    lib_en  varchar2(100)
);
insert into my_item_lib values (1, '1_fr', '1_en');
1 rows affected

insert into my_item_lib values (2, '2_fr', '2_en');
1 rows affected

insert into my_item_lib values (3, '3_fr', '3_en');
1 rows affected

insert into my_item_lib values (4, '4_fr', '4_en');
1 rows affected

insert into my_item_lib values (5, '5_fr', '5_en');
1 rows affected

insert into my_item_lib values (6, '6_fr', '6_en');
1 rows affected

insert into my_item_lib values (7, '7_fr', '7_en');
1 rows affected

insert into my_item_lib values (8, '8_fr', '8_en');
1 rows affected

insert into my_item_lib values (9, '9_fr', '9_en');
1 rows affected

insert into my_item_lib values (10, '10_fr', '10_en');
1 rows affected

create table my_item_hierarchy (
    id_item     number,
    id_item_sup number,
    ordre       number
);
insert into my_item_hierarchy values (1, null, 1);
1 rows affected

insert into my_item_hierarchy values (2, 1, 2);
1 rows affected

insert into my_item_hierarchy values (3, 2, 3);
1 rows affected

insert into my_item_hierarchy values (4, 3, 4);
1 rows affected

insert into my_item_hierarchy values (5, 3, 5);
1 rows affected

insert into my_item_hierarchy values (6, 4, 6);
1 rows affected

insert into my_item_hierarchy values (7, 4, 7);
1 rows affected

insert into my_item_hierarchy values (8, 2, 8);
1 rows affected

insert into my_item_hierarchy values (9, 1, 9);
1 rows affected

insert into my_item_hierarchy values (10, 9, 10);
1 rows affected

select i.id_item as iid, p.id_item as pid, g.id_item as gid, h.id_item as hid,
  lp.lib_en as lib_en_sup1,
  lg.lib_en as lib_en_sup2,
  lh.lib_en as lib_en_sup3  
from my_item_hierarchy i
left join my_item_hierarchy p on p.id_item = i.id_item_sup
left join my_item_hierarchy g on g.id_item = p.id_item_sup
left join my_item_hierarchy h on h.id_item = g.id_item_sup
left join my_item_lib lp on lp.id_item = p.id_item
left join my_item_lib lg on lg.id_item = g.id_item
left join my_item_lib lh on lh.id_item = h.id_item
order by 1


IID
PID
GID
HID
LIB_EN_SUP1
LIB_EN_SUP2
LIB_EN_SUP3


1
null
null
null
null
null
null

2
1
null
null
1_en
null
null

3
2
1
null
2_en
1_en
null

4
3
2
1
3_en
2_en
1_en

5
3
2
1
3_en
2_en
1_en

6
4
3
2
4_en
3_en
2_en

7
4
3
2
4_en
3_en
2_en

8
2
1
null
2_en
1_en
null

9
1
null
null
1_en
null
null

10
9
1
null
9_en
1_en
null

IID	PID	GID	HID	LIB_EN_SUP1	LIB_EN_SUP2	LIB_EN_SUP3
1	null	null	null	null	null	null
2	1	null	null	1_en	null	null
3	2	1	null	2_en	1_en	null
4	3	2	1	3_en	2_en	1_en
5	3	2	1	3_en	2_en	1_en
6	4	3	2	4_en	3_en	2_en
7	4	3	2	4_en	3_en	2_en
8	2	1	null	2_en	1_en	null
9	1	null	null	1_en	null	null
10	9	1	null	9_en	1_en	null