| 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 | STATIONERY PURCHASES | 6000 |
| 02 | 1 | 2 | OFFICE SUPPLIES PURCHASES | 2000 |
I want to obtain an output in the second table format using the table kept in the database as follows.
| 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 | |||
| GOODS AND SERVICES PURCHASE EXPENSES | SERVICE PROCUREMENT | 8000 | |
| GOODS AND SERVICES PURCHASE EXPENSES | SERVICE PROCUREMENT | STATIONERY PURCHASES | 6000 |
| GOODS AND SERVICES PURCHASE EXPENSES | SERVICE PROCUREMENT | OFFICE SUPPLIES PURCHASES | 2000 |
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>