Get credit and debit in a single row sql

Question

i am stuck in a situation to find accounts related report i need to get credit account and debit account in a single row..with amount

my table is like this

Id  VoucherId   AccountId   Amount  AccountName IsDebit
1   1           26          100     Sales       0
2   1           10          100     Cash        1
3   2           26          200     Sales       0
4   2           10          200     Cash        1
5   3           10          150     Cash        0
6   3           20          150     Expense A   1
7   4           10          240     Cash        0
8   4           21          240     Expense B   1

and i need to get the result like this VoucherId DebitName DebitID CreditName CreditID Amount
1 Cash 10 Sales 26 100 2 Cash 10 Sales 26 200 3 Expense a 10 Cash 26 150 4 Expense b 10 Cash 26 240 i tried this

select vc.Id,
am.Name,vc.AccountName,vd.[Description],
    case when vd.isdebit=1 then vd.amount else 0 end  as C6,
    case when vd.isdebit=1 then vd.AccountId else 0 end as A,
    case when vd.isdebit=0 then vd.amount else 0 end as C7,
    case when vd.isdebit=0 then vd.AccountId else 0 end as B
    from VoucherDetails vd 
    inner join Voucher Vc on vc.Id=vd.VoucherId and vc.IsDeleted=0
    inner join AccountsMaster Am on am.Id=vd.AccountId

and many other query, but not getting the above result

pls help.. thanks in Advance

Answer 1

This will work if you are doing double entry. That is for every debit entry there should be also a credit entry.

select DrVoucherId as VoucherId,DebitName,DebitId,CreditName,CreditId,Amount from (
select VoucherId as DrVoucherId,AccountName as DebitName ,AccountId as DebitId from VoucherDetails where IsDebit=1
) a
left join
(select VoucherId as CrVoucherId,AccountName as CreditName ,AccountId as CreditId,Amount from VoucherDetails where IsDebit=0) b on  a.DrVoucherId=b.CrVoucherId