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.

CASE status1 WHEN 1 THEN bal1 ELSE 0 END + CASE status2 WHEN 1 THEN bal2 ELSE 0 END + CASE status3 WHEN 1 THEN bal3 ELSE 0 END — Aaron Bertrand
– Aaron Bertrand, Commented Oct 20, 2014 at 3:36

Jeff Ogata · Accepted Answer · 2014-10-20 03:52:37Z

3

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

answered Oct 20, 2014 at 3:52

Jeff Ogata

58k20 gold badges117 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sqluser Over a year ago

Clever idea! Good on you @adrift

Jeff Ogata Over a year ago

Curious about the downvote. This will provide the same result as the multiple case statements, and I expect would perform better.

Aaron Bertrand Over a year ago

Wasn't me but do you have any evidence this will perform better? Even if it does, does it justify the cryptic and not-quite-self-documenting approach? What happens if the status columns change to a different type (like CHAR(1) to support 'Y'/'N' or TINYINT to support additional status types)?

radar · Accepted Answer · 2014-10-20 03:38:12Z

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

answered Oct 20, 2014 at 3:38

radar

13.4k2 gold badges27 silver badges33 bronze badges

Comments

Sudipto Maji · Accepted Answer · 2014-10-20 09:17:55Z

2

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

answered Oct 20, 2014 at 9:17

Sudipto Maji

411 bronze badge

Collectives™ on Stack Overflow

SQL Server : conditionally add rows and columns

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related