Split a row into columns in SQL

I want to obtain an output in the second table format using the table kept in the database as follows.

For this, I wrote a manual code in SQL as follows. But how can I make this more efficient?

SELECT 
CASE WHEN E1 = '01' PERSONNEL EXPENSES'
     WHEN E1 = '02' THEN 'GOODS AND SERVICES PURCHASE EXPENSES'
     ELSE NULL END AS A1,
     
CASE WHEN E1 = '01' AND E2 = '1' THEN 'OFFICERS'
     WHEN E1 = '02' AND E2 = '1' THEN 'SERVICE PROCUREMENT'
     ELSE NULL END AS A2,

AMOUNT 
FROM EXPENSES

How can I do this by taking from the column in the EXPENSES.EXPLANATION table without writing explanations like in this code?

Do you really use both PostgreSQL and Oracle? If so, I can't help much about the former, but - for Oracle, here's one option. Doesn't look very pretty, though.

Sample data suggests that this is about some kind of a hierarchy:

SQL> select * From test;

E1 E2 E3 EXPLANATION                              AMOUNT
-- -- -- ------------------------------------ ----------
01       personnel expenses                         5000
01 1     officers                                   5000
01 1  1  basic salaries                             3000
01 1  2  increases and compensation                 2000
02       goods and services purchase expenses       8000
02 1     service procurement                        8000
02 1  1  stationary purchases                       6000
02 1  2  office supplies purchases                  2000

8 rows selected.

Read comments within code; they explain what each CTE (and final SELECT) do:

SQL> with temp as
  2  -- create hierarchy (two columns, PARENT and CHILD)
  3  (          select 'X' e1      , null e2     , 'master row' explanation, 0 amount from dual
  4   union all select e1          , 'X' e2      , explanation             ,   amount from test where e2 is null
  5   union all select e1 ||'-'||e2, e1          , explanation             ,   amount from test where e2 is not null and e3 is null
  6   union all select e3          , e1 ||'-'||e2, explanation             ,   amount from test where e2 is not null and e3 is not null
  7  ),
  8  temp2 as
  9  -- SYS_CONNECT_BY_PATH creates a whole path of all explanations
 10  (select level lvl,
 11     sys_connect_by_path(explanation, '/') pt,
 12     amount
 13   from temp
 14   where level > 1
 15   start with e2 is null
 16   connect by nocycle prior e1 = e2
 17  )
 18  -- finally, extract separate result columns
 19  select
 20         substr(pt, instr(pt, '/', 1, 2) + 1,
 21                    case when lvl = 2 then length(pt) else instr(pt, '/', 1, 3) - instr(pt, '/', 1, 2) - 1 end
 22               ) a1,
 23         substr(pt, instr(pt, '/', 1, 3) + 1,
 24                    case when lvl = 3 then length(pt) else instr(pt, '/', 1, 4) - instr(pt, '/', 1, 3) - 1 end
 25               ) a2,
 26         substr(pt, instr(pt, '/', 1, 4) + 1,
 27                    case when lvl = 4 then length(pt) else instr(pt, '/', 1, 5) - instr(pt, '/', 1, 4) - 1 end
 28               ) a3,
 29         amount
 30  from temp2 t;

Result:

A1                                   A2                        A3                                       AMOUNT
------------------------------------ ------------------------- ------------------------------------ ----------
personnel expenses                                                                                        5000
personnel expenses                   officers                                                             5000
personnel expenses                   officers                  basic salaries                             3000
personnel expenses                   officers                  increases and compensation                 2000
goods and services purchase expenses                                                                      8000
goods and services purchase expenses service procurement                                                  8000
goods and services purchase expenses service procurement       stationary purchases                       6000
goods and services purchase expenses service procurement       office supplies purchases                  2000

8 rows selected.

SQL>