SQL Server : merging two rows to one row

Viewed 25

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

enter image description here

0 Answers
Related