Please how I reformat my code to merge the two rows in to one row. They are all related
with edet as (
select
fVoucherDtl.SerialNo,
fVoucherDtl.PayeePayer,
fVoucherDtl.PartyCode,
fVoucherDtl.AccountCode,
fVoucherDtl.TransactionAmount,
max( CASE TRY_CAST(fVoucherDtl.SubSerialNo AS NUMERIC)
WHEN 1
THEN CAST(CAST(fVoucherDtl.TransactionAmount AS NUMERIC) AS
VARCHAR(100))
ELSE ''
END) AS DR,
max( CASE TRY_CAST(fVoucherDtl.SubSerialNo AS NUMERIC)
WHEN 2
THEN CAST(CAST(fVoucherDtl.TransactionAmount AS
NUMERIC) AS VARCHAR(100))
ELSE ''
END) AS CR
from fVoucherDtl
group by
fVoucherDtl.SerialNo,
fVoucherDtl.PayeePayer,
fVoucherDtl.PartyCode,
fVoucherDtl.AccountCode,
fVoucherDtl.TransactionAmount
)
select
fvoucherhdr.SerialNo,
fvoucherhdr.VoucherNo,
fvoucherhdr.Narration,
fvoucherhdr.VoucherDate,
edet.DR,
edet.CR,
edet.PayeePayer,
edet.PartyCode,
edet.AccountCode
from fvoucherhdr
--inner join edet on edet.SerialNo = fvoucherhdr.SerialNo
inner join edet on edet.SerialNo = fvoucherhdr.SerialNo
where voucherdate between '2020/10/01' and '2022/02/02'
and fVoucherHdr.cancelled = 'N'
and fVoucherHdr.TransactionType = 'P'
and PartyCode <> 0
and fVoucherHdr.SerialNo = 3402856
