Programming Languages on Power

Hi David,

the easiest way is, to JOIN your table against a CTE with "indexes" and use CASE expressions to pivot your data.

with array_idx (idx) as (
    values 1, 2, 3, 4, 5
)
select entry_id,
       case idx
         when 1 then fund1
         when 2 then fund2
         when 3 then fund3
         when 4 then fund4
         when 5 then fund5
       end,
       case idx
         when 1 then debit1
         when 2 then debit2
         when 3 then debit3
         when 4 then debit4
         when 5 then debit5
       end
from your_table
join array_idx on true
order by entry_id, idx;

I know, those CASE expressions are annoying - but it's the only way, to pivot data from "columns" to "rows" or the other way around.

I have assumed, that you table is called "your_table" and I create the CTE "on the fly" with the WITH clause.

Of course - if you have monthly or other data, you can also join another table, that gives you a columns "index".

HTH and kind regards,

Daniel

Not tested, but you may also try this:

Select EntryId, Fund, Debit
  from YourTable
       Cross Join Lateral(Values(1, Fund1, Debit1)),
                                (2, Fund2, Debit2)),
                                (3, Fund3, Debig3)),
                                (4, Fund4, Debit4)),
                                (5, Fund5, Debit5)) x (Counter, Fund, Debit);

So, Birgitta's Solution worked with a couple of minor tweaks:

Select KeyDate, KeyTime, Fund, Func, Dept, Divn, Acct, Desc, Debit, Credit
from bafile ba
Cross Join Lateral(Values(1, ba.Fd1, ba.Fc1, ba.Dp1, ba.Dv1, ba.Ac1, ba.Ds1, ba.Dr1, ba.Cr1),
                         (2, ba.Fd2, ba.Fc2, ba.Dp2, ba.Dv2, ba.Ac2, ba.Ds2, ba.Dr2, ba.Cr2),
                         (3, ba.Fd3, ba.Fc3, ba.Dp3, ba.Dv3, ba.Ac3, ba.Ds3, ba.Dr3, ba.Cr3),
                         (4, ba.Fd4, ba.Fc4, ba.Dp4, ba.Dv4, ba.Ac4, ba.Ds4, ba.Dr4, ba.Cr4),
                         (5, ba.Fd5, ba.Fc5, ba.Dp5, ba.Dv5, ba.Ac5, ba.Ds5, ba.Dr5, ba.Cr5),
                         (6, ba.Fd6, ba.Fc6, ba.Dp6, ba.Dv6, ba.Ac6, ba.Ds6, ba.Dr6, ba.Cr6),
                         (7, ba.Fd7, ba.Fc7, ba.Dp7, ba.Dv7, ba.Ac7, ba.Ds7, ba.Dr7, ba.Cr7),
                         (8, ba.Fd8, ba.Fc8, ba.Dp8, ba.Dv8, ba.Ac8, ba.Ds8, ba.Dr8, ba.Cr8),
                         (9, ba.Fd9, ba.Fc9, ba.Dp9, ba.Dv9, ba.Ac9, ba.Ds9, ba.Dr9, ba.Cr9),
                         (10, ba.Fd10, ba.Fc10, ba.Dp10, ba.Dv10, ba.Ac10, ba.Ds10, ba.Dr10, ba.Cr10),
                         (11, ba.Fd11, ba.Fc11, ba.Dp11, ba.Dv11, ba.Ac11, ba.Ds11, ba.Dr11, ba.Cr11),
                         (12, ba.Fd12, ba.Fc12, ba.Dp12, ba.Dv12, ba.Ac12, ba.Ds12, ba.Dr12, ba.Cr12),
                         (13, ba.Fd13, ba.Fc13, ba.Dp13, ba.Dv13, ba.Ac13, ba.Ds13, ba.Dr13, ba.Cr13),
                         (14, ba.Fd14, ba.Fc14, ba.Dp14, ba.Dv14, ba.Ac14, ba.Ds14, ba.Dr14, ba.Cr14),
                         (15, ba.Fd15, ba.Fc15, ba.Dp15, ba.Dv15, ba.Ac15, ba.Ds15, ba.Dr15, ba.Cr15),
                         (16, ba.Fd16, ba.Fc16, ba.Dp16, ba.Dv16, ba.Ac16, ba.Ds16, ba.Dr16, ba.Cr16)) 
                         x (Counter, Fund, Func, Dept, Divn, Acct, Desc, Debit, Credit)
where fund > 0 
order by KeyDate, KeyTime   
;

had to qualify the field names and remove the double right paren on each line except the last one, then works like magic!!

You can use also a simple union

select 1, entryid, fund1, debit1

union all

select 2, entryid, fund2, debit2

[...]

Hi,

Like Brigitta i use LATERAL :

CREATE TABLE MY_TABLE (entry_id DEC(2,0), 
                       fund1 char(10), 
                       debit1 DEC(5,2),
                       fund2 char(10), 
                       debit2 DEC(5,2),
                       fund3 char(10), 
                       debit3 DEC(5,2),
                       fund4 char(10), 
                       debit4 DEC(5,2),
                       fund5 char(10), 
                       debit5 DEC(5,2) );
                              
insert into MY_TABLE VALUES(1, 'FUND_A', 100, 'FUND_B', 0.10, 'FUND_C', 15.55, 'FUND_D', 10 , 'FUND_E', 1);       
insert into MY_TABLE VALUES(2, 'FUND_A', 200, 'FUND_B', 0.20, 'FUND_C', 25.55, 'FUND_D', 20 , 'FUND_E', 2); 
insert into MY_TABLE VALUES(3, 'FUND_A', 300, 'FUND_B', 0.30, 'FUND_C', 35.55, 'FUND_D', 30 , 'FUND_E', 3); 
insert into MY_TABLE VALUES(4, 'FUND_A', 400, 'FUND_B', 0.40, 'FUND_C', 45.55, 'FUND_D', 40 , 'FUND_E', 4);

Select * from MY_TABLE; 

WITH CTE_LATERAL AS(
    SELECT A.ENTRY_ID, L.FUND, L.DEBIT
    FROM MY_TABLE A,
    LATERAL(VALUES (A.FUND1, A.DEBIT1),
                   (A.FUND2, A.DEBIT2),
                   (A.FUND3, A.DEBIT3),
                   (A.FUND4, A.DEBIT4),
                   (A.FUND5, A.DEBIT5)
           ) AS L(FUND, DEBIT)
                   
    )
SELECT * FROM CTE_LATERAL;

Without CTE it's OK too...

SELECT A.ENTRY_ID, L.FUND, L.DEBIT
    FROM PGCLEMENT.DAVID A,
    LATERAL(VALUES (A.FUND1, A.DEBIT1),
                   (A.FUND2, A.DEBIT2),
                   (A.FUND3, A.DEBIT3),
                   (A.FUND4, A.DEBIT4),
                   (A.FUND5, A.DEBIT5)
           ) AS L(FUND, DEBIT);