Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
create table my_item_lib
(
    id_item number,
    lib_fr  varchar2(100),
    lib_en  varchar2(100)
);
create table my_item_hierarchy
(
    id_item     number,
    id_item_sup number,
    ordre       number
);
INSERT ALL
  INTO my_item_lib VALUES (1, '1_fr', '1_en')
  INTO my_item_lib VALUES (2, '2_fr', '2_en')
  INTO my_item_lib VALUES (3, '3_fr', '3_en')
  INTO my_item_lib VALUES (4, '4_fr', '4_en')
  INTO my_item_lib VALUES (5, '5_fr', '5_en')
  INTO my_item_lib VALUES (6, '6_fr', '6_en')
  INTO my_item_lib VALUES (7, '7_fr', '7_en')
  INTO my_item_lib VALUES (8, '8_fr', '8_en')
  INTO my_item_lib VALUES (9, '9_fr', '9_en')
  INTO my_item_lib VALUES (10, '10_fr', '10_en')
  INTO my_item_hierarchy VALUES (1, null, 1)
  INTO my_item_hierarchy VALUES (2, 1, 2)
  INTO my_item_hierarchy VALUES (3, 2, 3)
  INTO my_item_hierarchy VALUES (4, 3, 4)
  INTO my_item_hierarchy VALUES (5, 3, 5)
  INTO my_item_hierarchy VALUES (6, 4, 6)
  INTO my_item_hierarchy VALUES (7, 4, 7)
  INTO my_item_hierarchy VALUES (8, 2, 8)
  INTO my_item_hierarchy VALUES (9, 1, 9)
  INTO my_item_hierarchy VALUES (10, 9, 10)
SELECT * FROM DUAL;
20 rows affected

select *
from
(
    with v00 as
    (
        select  
        a.id_item       
        , lib.lib_fr
        , lib.lib_en
        -- --
        , a.id_item_sup
        , lib_sup.lib_fr lib_fr_sup
        , lib_sup.lib_en lib_en_sup
        from my_item_hierarchy a
        , my_item_lib lib
        , my_item_lib lib_sup
        where 1 = 1
        and a.id_item = lib.id_item
        and a.id_item_sup = lib_sup.id_item(+)
    ), h as
    (
        select 
        connect_by_root id_item id_item
        , id_item_sup
        , level lvl
        from v00
        where 1 = 1
        connect by 
        id_item = prior id_item_sup 
        and level <= 3
    )
    select id_item
    , id_item_sup1, id_item_sup2, id_item_sup3
    from h
    pivot (max(id_item_sup) for lvl in (1 as id_item_sup1, 2 as id_item_sup2, 3 as id_item_sup3))
    order by 1
)
;


ID_ITEM
ID_ITEM_SUP1
ID_ITEM_SUP2
ID_ITEM_SUP3


1
null
null
null

2
1
null
null

3
2
1
null

4
3
2
1

5
3
2
1

6
4
3
2

7
4
3
2

8
2
1
null

9
1
null
null

10
9
1
null


SELECT "1_ID_ITEM" AS id_item,
       "2_ID_ITEM" AS id_item_sup1,
       "3_ID_ITEM" AS id_item_sup2,
       "4_ID_ITEM" AS id_item_sup3,
       "1_LIB_EN" AS lib_en,
       "2_LIB_EN" AS lib_en_sup1,
       "3_LIB_EN" AS lib_en_sup2,
       "4_LIB_EN" AS lib_en_sup3,
       "1_LIB_FR" AS lib_fr,
       "2_LIB_FR" AS lib_fr_sup1,
       "3_LIB_FR" AS lib_fr_sup2,
       "4_LIB_FR" AS lib_fr_sup3
FROM   (
  SELECT CONNECT_BY_ROOT l.id_item AS root_item,
         l.id_item,
         l.lib_en,
         l.lib_fr,
         LEVEL AS lvl
  FROM   my_item_hierarchy h
         INNER JOIN my_item_lib l
         ON (h.id_item = l.id_item)
  WHERE  LEVEL <= 4
  CONNECT BY l.id_item = PRIOR h.id_item_sup
)
PIVOT (
  MAX(id_item) AS id_item,
  MAX(lib_en) AS lib_en,
  MAX(lib_fr) AS lib_fr
  FOR lvl IN (1,2,3,4)
)
ORDER BY id_item;


ID_ITEM
ID_ITEM_SUP1
ID_ITEM_SUP2
ID_ITEM_SUP3
LIB_EN
LIB_EN_SUP1
LIB_EN_SUP2
LIB_EN_SUP3
LIB_FR
LIB_FR_SUP1
LIB_FR_SUP2
LIB_FR_SUP3


1
null
null
null
1_en
null
null
null
1_fr
null
null
null

2
1
null
null
2_en
1_en
null
null
2_fr
1_fr
null
null

3
2
1
null
3_en
2_en
1_en
null
3_fr
2_fr
1_fr
null

4
3
2
1
4_en
3_en
2_en
1_en
4_fr
3_fr
2_fr
1_fr

5
3
2
1
5_en
3_en
2_en
1_en
5_fr
3_fr
2_fr
1_fr

6
4
3
2
6_en
4_en
3_en
2_en
6_fr
4_fr
3_fr
2_fr

7
4
3
2
7_en
4_en
3_en
2_en
7_fr
4_fr
3_fr
2_fr

8
2
1
null
8_en
2_en
1_en
null
8_fr
2_fr
1_fr
null

9
1
null
null
9_en
1_en
null
null
9_fr
1_fr
null
null

10
9
1
null
10_en
9_en
1_en
null
10_fr
9_fr
1_fr
null


  ID_ITEM ID_ITEM_SUP1 ID_ITEM_SUP2 ID_ITEM_SUP3 LIB_EN_SUP1     LIB_EN_SUP2     LIB_EN_SUP3
---------- ------------ ------------ ------------ --------------- --------------- ---------------
         1                                        
         2            1                           1_en
         3            2            1              2_en            1_en           
         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              2_en            1_en   
         9            1                           1_en
        10            9            1              9_en            1_en
ORA-00900: invalid SQL statement
ID_ITEM	ID_ITEM_SUP1	ID_ITEM_SUP2	ID_ITEM_SUP3
1	null	null	null
2	1	null	null
3	2	1	null
4	3	2	1
5	3	2	1
6	4	3	2
7	4	3	2
8	2	1	null
9	1	null	null
10	9	1	null