Postgresql - Split a row into columns in SQL

Coder
October 5, 2023
273 views
1 vote
2 Answers

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?

Answers

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>

If it is Oracle then you could use MODEL clause like below. It is fast and reliable.

WITH      --  S a m p l e    D a t a 
    tbl (E1, E2, E3, EXPLANATION,   AMOUNT) AS
        (   Select '01', null, null, 'PERSONNEL EXPENSES', 5000 From Dual Union All
            Select '01',    1, null, 'OFFICERS', 5000 From Dual Union All
            Select '01',    1,    1, 'BASIC SALARIES', 3000 From Dual Union All
            Select '01',    1,    2, 'INCREASES AND COMPENSATION', 2000 From Dual Union All
            Select '02', null, null, 'GOODS AND SERVICES PURCHASE EXPENSES', 8000 From Dual Union All
            Select '02',    1, null, 'SERVICE PROCUREMENT', 8000 From Dual Union All
            Select '02',    1,    1, 'STATIONERY PURCHASES', 6000 From Dual Union All
            Select '02',    1,    2, 'OFFICE SUPPLIES PURCHASES', 2000 From Dual 
        )
--  M a i n    S Q L :
Select      A1, A2, A3, AMOUNT
From          ( Select  E1, E2, E3, EXPLANATION, EXPLANATION "A1",  EXPLANATION "A2", EXPLANATION "A3", AMOUNT
                From tbl 
              )
    MODEL   Partition By (E1)
            Dimension By (E2, E3)
            Measures     (A1, A2, A3, AMOUNT) 
   RULES  ( A1[ANY, ANY] = A1[Null, Null],
            A2[ANY, ANY] = Case When CV(E2) Is Null Then Null 
                           Else A2[CV(), null] 
                           End,
            A3[ANY, ANY] = Case When CV(E3) Is Null Then Null
                           Else A3[CV(), CV()]
                           End
              )
Order By E1, E2 Nulls First, E3 Nulls First
/*
--  R e s u l t :
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                  STATIONERY PURCHASES                       6000
GOODS AND SERVICES PURCHASE EXPENSES SERVICE PROCUREMENT                  OFFICE SUPPLIES PURCHASES                  2000
*/

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – Split a row into columns in SQL

Answers