SQL Server : conditionally add rows and columns

Question

I have a column as

--------------------------------------------------------------
| Sl. No. | bal1 | bal2 | bal3 | status1 | status2 | status3 |
--------------------------------------------------------------
|    1    | 520  | 270  | 351  |    1    |    0     |    1   |
|    2    | 201  | 456  | 154  |    0    |    1     |    1   |
--------------------------------------------------------------

I would like to add the rows field for Status value = 1 in SQL Server

eg. result

--------------------
| Sl. No. | amount |
--------------------
|    1    |   871  | // bal1 + bal3 as the status1 and status3 is 1
|    2    |   610  | // bal2 + bal3 as the status2 and status3 is 1
--------------------

Thanks in advance.

Answer 1

If the status values will always be 1 or 0, you can multiply and add:

select [Sl. No.], bal1 * status1 + bal2 * status2 + bal3 * status3
from table

Answer 2

you can do it using CASE

SQL Fiddle

SELECT [SI. No.], 
       (case when status1 =1 then bal1 else 0 end +
       case when status2 =1 then bal2 else 0 end +
       case when status3 =1 then bal3 else 0 end)  as balance

from Table1

Answer 3

SELECT [Sl. No.], 
   (case when bs.[status1] =1 then bs.bal1 else 0 end +
   case when bs.status2 =1 then bs.bal2 else 0 end +
   case when bs.status3 =1 then bs.bal3 else 0 end)  as amount
from BalStatus AS bs