Subtract values from one column based on values in another (TSQL)

I have table where I need to subtract values in one column based on conditions applied to the other. For example, I want to subtract values with code fst and trd, meaning (12 - 23). I don’t want to declare separate variables. Is it possible to make this with a query?

Answer

One method is conditional aggregation:

select sum(case when code = 'fst' then value else - value end)
from t
where code in ('fst', 'trd');

Assuming you have only one row for each code, you can also use a join:

select tf.value - tt.value
from t tf join
     t tt
     on tf.code = 'fst' and tt.code = 'trd'