Mohammad Sadiqur Rahman Mohammad Sadiqur Rahman - 28 days ago 3
SQL Question

How can "column in different row" be placed "different column in single row"

I want to group by two row and value of different two row of same column want to placed in different columns in a single row.below is my table
enter image description here

For example, I want no 1 & 2 row in a single row.In a single row,there will be ref_no(same in both row),by(LEDGERNAME whose VOUCHER_BY is Dr),to(LEDGERNAME whose VOUCHER_BY is Cr),AMOUNT(same in both row) or like this--
enter image description here

I used oracle database.How can i do this?

Answer

Simple. Self join:

Select d.ref_no, d.ledgername by, c.ledgername to, d.amount from my_ledger_table d, my_ledger_table c
Where d.ref_no =c.ref_no
And d.voucher_by = 'Dr'
And c.voucher_by = 'Cr'
And d.amount = c.amount;