Postgres 14

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

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table "B12_Expense_AB" (
  "rep_Month" date,
  "p_Code" text,
  "L1" text, "L2" text, "L3" text, "L4" text, "L5" text, "L6" text,
  "EX_1706" int, "EX_1707" int, "EX_1708" int
);

insert into "B12_Expense_AB" values
('2017-08-01', 'Line 1', 'L11', 'L12', 'L13', 'L14', 'L15', 'L16', '16', '17', '18'),
('2017-08-01', 'Line 2', 'L21', 'L22', 'L23', 'L24', 'L25', 'L26', '26', '27', '28');

select * from "B12_Expense_AB";
2 rows affected



rep_Month
p_Code
L1
L2
L3
L4
L5
L6
EX_1706
EX_1707
EX_1708


2017-08-01
Line 1
L11
L12
L13
L14
L15
L16
16
17
18

2017-08-01
Line 2
L21
L22
L23
L24
L25
L26
26
27
28


select e."rep_Month", e."p_Code", 
       e."L1", e."L2", e."L3", e."L4", e."L5", e."L6",
       regexp_replace(j.k, 'EX_(\d\d)(\d\d)', '20\1-\2-01')::date as "Exp_Month", 
       j.v as "Expense"
  from "B12_Expense_AB" e
       join lateral jsonb_each_text(to_jsonb(e)) as j(k, v)
         on j.k like 'EX\_%';


rep_Month
p_Code
L1
L2
L3
L4
L5
L6
Exp_Month
Expense


2017-08-01
Line 1
L11
L12
L13
L14
L15
L16
2017-06-01
16

2017-08-01
Line 1
L11
L12
L13
L14
L15
L16
2017-07-01
17

2017-08-01
Line 1
L11
L12
L13
L14
L15
L16
2017-08-01
18

2017-08-01
Line 2
L21
L22
L23
L24
L25
L26
2017-06-01
26

2017-08-01
Line 2
L21
L22
L23
L24
L25
L26
2017-07-01
27

2017-08-01
Line 2
L21
L22
L23
L24
L25
L26
2017-08-01
28

rep_Month	p_Code	L1	L2	L3	L4	L5	L6	EX_1706	EX_1707	EX_1708
2017-08-01	Line 1	L11	L12	L13	L14	L15	L16	16	17	18
2017-08-01	Line 2	L21	L22	L23	L24	L25	L26	26	27	28

rep_Month	p_Code	L1	L2	L3	L4	L5	L6	Exp_Month	Expense
2017-08-01	Line 1	L11	L12	L13	L14	L15	L16	2017-06-01	16
2017-08-01	Line 1	L11	L12	L13	L14	L15	L16	2017-07-01	17
2017-08-01	Line 1	L11	L12	L13	L14	L15	L16	2017-08-01	18
2017-08-01	Line 2	L21	L22	L23	L24	L25	L26	2017-06-01	26
2017-08-01	Line 2	L21	L22	L23	L24	L25	L26	2017-07-01	27
2017-08-01	Line 2	L21	L22	L23	L24	L25	L26	2017-08-01	28