Creating Column Based On Where Condition

I've the following query: SELECT tn.Date, b1.Name DrBook, b2.Name CrBook, c1.Name DrControl, c2.Name CrControl, l1.Name DrLedger, l2.Name CrLedger, s1.Name DrSubLedg

Solution 1:

You can use case expressions for this:

select 
    ...,
    casewhen tn.CrControl = 7then tn.Amount endas debit,
    casewhen tn.DrControl = 7then tn.Amount endas credit
from ...
where7in (tn.DrControl, tn.CrControl)

Note that I also rewrote your where clause to use in instead of or, which makes it a little shorter.