Subquery to display one column in a Table as two separate columns

Question

I have two tables

Ledgers Table
==============
ledger_id  ledger_name  open_bal  close_bal  dr_bal   cr_bal
---------  ---------   ---------  ---------   ------   ------  
1          Bank A/C        0       5000       5000      0              
2          Capital A/C     0      -50000      0         50000          
3          Cash A/C        0       30700      53500     22800      


Transactions Table 
==============
trans_id  trans_date  ledger_id  ledger_name   amount   trans_type 
---------  ---------  ---------  ---------    ---------  ---------  
1         2004-01-01   3          Cash A/C      50000  Dr                   
2         2004-01-01   2          Cap A/C       50000  Cr                    
3         2004-01-02   9          Purchase A/C  10000  Dr                   

These are my tables, what am trying to achieve is to get the ledgers balances for a particular month. Here am not trying to insert data into a table.

Using the above two tables i just need to query and out put the result as

Desired Output
==============      

ledger_id   ledger_name  amount trans_type As Debit   trans_type as Credit
---------   ---------    ------  ---------            --------- 
3           Cash A/C     50000        Dr                Null
2           Capital A/C  50000      Null                  Cr

So trans_type field here is displayed separately DR as Debits and Cr as Credits. This is what I want to achieve.

What i have tried till now is joining the transaction table with the ledgers! But i have failed to get the desired output just by querying these two tables.

This is what i have tried,

SELECT tr.trans_date, tr.amount, tr.ledger_id, l.ledger_name, tr.trans_type
FROM tbl_transaction tr LEFT JOIN tbl_ledgers l 
ON l.ledger_id = tr.ledger_id WHERE trans_date BETWEEN '2004-01-01' AND '2004-01-31'; 

So i basically want to know how can we achieve this? trans_type column split into two "Dr" separate and "Cr" separate?

Answer 1

You may use a CASE to inspect the actual value of trans_type and display it, doing so once for each alias Debit, Credit. This gets unwieldy with more than a few values, but since you have just two it is a simple method.

SELECT 
  tr.ledger_id,
  l.ledger_name,
  tr.amount,
  CASE WHEN tr.trans_type = 'Dr' THEN tr.trans_type ELSE NULL END AS Debit,
  CASE WHEN tr.trans_type = 'Cr' THEN tr.trans_type ELSE NULL END AS Credit
FROM
  tbl_transaction tr
  LEFT JOIN tbl_ledgers l ON l.ledger_id = tr.ledger_id
WHERE
 trans_date BETWEEN '2004-01-01' AND '2004-01-31'; 

Here is a demonstration: http://sqlfiddle.com/#!9/f5623/2

Note that this uses a LEFT JOIN, resulting in ledger_id = 9 being returned in the demonstration (different than your example). Changing it to an INNER JOIN would correct that. (http://sqlfiddle.com/#!9/f5623/3)