Transform void record based on matching non void record

I have to transform 4 fields when certain conditions happen.
The VOID (type = 6) lines have to be transformed with the non VOID (type 0,2). The Transaction is unique for each line and the VOID line has the originating Transaction in the Voidtran field.

Here’s my query:

SELECT   CAST(payhist.number AS CHAR) Transaction
        ,CAST(payhist.descr AS CHAR) TransactionDesc
        ,CAST(payhist.account AS CHAR) Account
        ,CAST(payhist.type AS CHAR) Type
        ,CAST(payhist.voidtran AS CHAR) Voidtran
        ,CAST(servdef.number AS CHAR) ServDefNum
        ,CAST(servdef.descr AS CHAR) ServDefDesc
        ,CAST(LEFT(list_charge.descr,5) AS CHAR) ChargeType
        ,CAST(list_charge.descr AS CHAR) ChargeTypeDesc
FROM billmax.payhist 
    LEFT JOIN monthlysale ON  monthlysale.payhist = payhist.number
    LEFT JOIN servdef ON payhist.servdef = servdef.number 
    LEFT JOIN lists AS list_charge ON list_charge.value = payhist.charge_type AND list_charge.list='chargetypes'
WHERE payhist.type IN (0,2,6) 
AND monthlysale.bookdate BETWEEN '2020-12-01' AND NOW()
AND monthlysale.amount <>0
AND payhist.number IN(9751739,9729411) 

Here is what the data looks like and how I want the outcome to look:

I think I need to build a UNION query – one part for Type 0 or 2 and one part for Type 6 but I don’t know how to change the 4 fields. All help is greatly appreciated!

Answer

If I understand correctly, you want to “spread” values when one is NULL. This uses COALESCE() and window functions:

SELECT COALESCE(payhist.number, MAX(payhist.number) OVER (PARTITION BY payhist.account)) as Transaction
       payhist.descras as TransactionDesc
       payhist.account as Account
       COALESCE(payhist.type, MAX(payhist.type) OVER (PARTITION BY payhist.account) as Type,
      . . .
FROM billmax.payhist JOIN
     monthlysale
     ON  monthlysale.payhist = payhist.number LEFT JOIN
     servdef
     ON payhist.servdef = servdef.number LEFT JOIN
     lists AS list_charge
     ON list_charge.value = payhist.charge_type AND list_charge.list = 'chargetypes'
WHERE payhist.type IN (0,2,6) AND
      monthlysale.bookdate BETWEEN '2020-12-01' AND NOW() AND
      monthlysale.amount <> 0 AND
      payhist.number IN (9751739, 9729411) ;

I have no idea why you are casting values to strings (especially values that seem to already be strings), so I just removed that logic.