Is there a shorter way to write this repetitive PostgreSQL code?-CodePudding

I have 2 related questions and will mark this post as solved for any valid reply to any one of them.

Question 1

I'm quite new at PostgreSQL. Is there a shorter way to write this PostgreSQL code without the repetitive SQL code blocks ? I'm using pgAdmin (if it makes any difference)

Maybe Blocks of SQL code between Union All commands can be defined to be a repetitive template, then they can be repeated by using months as parameters.

Question 2

What I'm trying to do here is turning a table like;

rep_Month | p_Code | L1 .... L6 | EX_1706 | EX_1707 | EX_1708 .... EX_1712

to;

rep_Month | p_Code | L1 .... L6 | DATE | Expense

Can it be done with a more suitable approach ? (I guess there may be one but could not figure it out.)

Thanks in advance...

Have a great day...

-- 2017 --------------------------------------------------

    SELECT 
        "B12_Expense_AB"."rep_Month",
        "B12_Expense_AB"."p_Code",
        "B12_Expense_AB"."L1",
        "B12_Expense_AB"."L2",
        "B12_Expense_AB"."L3",
        "B12_Expense_AB"."L4",
        "B12_Expense_AB"."L5",
        "B12_Expense_AB"."L6",
        DATE '2017-06-01' AS "Exp_Month",   
        "B12_Expense_AB"."EX_1706" AS "Expense"

    FROM 
        "B12_Expense_AB"

    WHERE 
        "B12_Expense_AB"."EX_1706" <> 0
    
UNION ALL
    
    SELECT 
        "B12_Expense_AB"."rep_Month",
        "B12_Expense_AB"."p_Code",
        "B12_Expense_AB"."L1",
        "B12_Expense_AB"."L2",
        "B12_Expense_AB"."L3",
        "B12_Expense_AB"."L4",
        "B12_Expense_AB"."L5",
        "B12_Expense_AB"."L6",
        DATE '2017-07-01' AS "Exp_Month",   
        "B12_Expense_AB"."EX_1707" AS "Expense"

    FROM 
        "B12_Expense_AB"

    WHERE 
        "B12_Expense_AB"."EX_1707" <> 0
        
UNION ALL
    
    SELECT 
        "B12_Expense_AB"."rep_Month",
        "B12_Expense_AB"."p_Code",
        "B12_Expense_AB"."L1",
        "B12_Expense_AB"."L2",
        "B12_Expense_AB"."L3",
        "B12_Expense_AB"."L4",
        "B12_Expense_AB"."L5",
        "B12_Expense_AB"."L6",
        DATE '2017-08-01' AS "Exp_Month",   
        "B12_Expense_AB"."EX_1708" AS "Expense"

    FROM 
        "B12_Expense_AB"

    WHERE 
        "B12_Expense_AB"."EX_1708" <> 0
        
UNION ALL
    
    SELECT 
        "B12_Expense_AB"."rep_Month",
        "B12_Expense_AB"."p_Code",
        "B12_Expense_AB"."L1",
        "B12_Expense_AB"."L2",
        "B12_Expense_AB"."L3",
        "B12_Expense_AB"."L4",
        "B12_Expense_AB"."L5",
        "B12_Expense_AB"."L6",
        DATE '2017-09-01' AS "Exp_Month",   
        "B12_Expense_AB"."EX_1709" AS "Expense"

    FROM 
        "B12_Expense_AB"

    WHERE 
        "B12_Expense_AB"."EX_1709" <> 0
        
UNION ALL
    
    SELECT 
        "B12_Expense_AB"."rep_Month",
        "B12_Expense_AB"."p_Code",
        "B12_Expense_AB"."L1",
        "B12_Expense_AB"."L2",
        "B12_Expense_AB"."L3",
        "B12_Expense_AB"."L4",
        "B12_Expense_AB"."L5",
        "B12_Expense_AB"."L6",
        DATE '2017-10-01' AS "Exp_Month",   
        "B12_Expense_AB"."EX_1710" AS "Expense"

    FROM 
        "B12_Expense_AB"

    WHERE 
        "B12_Expense_AB"."EX_1710" <> 0
        
UNION ALL
    
    SELECT 
        "B12_Expense_AB"."rep_Month",
        "B12_Expense_AB"."p_Code",
        "B12_Expense_AB"."L1",
        "B12_Expense_AB"."L2",
        "B12_Expense_AB"."L3",
        "B12_Expense_AB"."L4",
        "B12_Expense_AB"."L5",
        "B12_Expense_AB"."L6",
        DATE '2017-11-01' AS "Exp_Month",   
        "B12_Expense_AB"."EX_1711" AS "Expense"

    FROM 
        "B12_Expense_AB"

    WHERE 
        "B12_Expense_AB"."EX_1711" <> 0
        
UNION ALL
    
    SELECT 
        "B12_Expense_AB"."rep_Month",
        "B12_Expense_AB"."p_Code",
        "B12_Expense_AB"."L1",
        "B12_Expense_AB"."L2",
        "B12_Expense_AB"."L3",
        "B12_Expense_AB"."L4",
        "B12_Expense_AB"."L5",
        "B12_Expense_AB"."L6",
        DATE '2017-12-01' AS "Exp_Month",   
        "B12_Expense_AB"."EX_1712" AS "Expense"

    FROM 
        "B12_Expense_AB"

    WHERE 
        "B12_Expense_AB"."EX_1712" <> 0;

CodePudding user response：

Pivoting columns to rows can be done using the jsonb functions. Try something like this:

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\_%';

db<>fiddle here