CODEWITHSUNDEEP
sqlsql-server
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6960

Concise test for zero or NULL for multiple columns

I would like to make test for multiple columns if they have a positive number and return results in a binary string. Single test condition yield 0 if either =0 or is NULL. Say, we have a 3 conditions A, B, and C which return either 0 or 1. The result i.e., 101 means that A and C have positive numbers while B is either zero or null.

This gets what I want:

SELECT
    format(
                 iif(coalesce([A], 0) > 0, 100, 0)
               + iif(coalesce([B], 0) > 0,  10, 0) 
               + iif(coalesce([C], 0) > 0,   1, 0)
              ), '000'
          )

Is there a more concise way to achieve the goal, perhaps avoiding COALESCE?

Upvotes: 0

Views: 44

Answers (1)

Thom A
Thom A

Reputation: 96038

I'd just go for CONCAT if I am honest, it's far more performant than FORMAT, and remove the COALESCE as it isn't needed.

SELECT CONCAT(IIF(A>0,1,0),IIF(B>0,1,0),IIF(C>0,1,0))
FROM (VALUES(1,NULL,2),
            (-1,12,18),
            (-1,0,1),
            (NULL,NULL,-4))V(A,B,C);

Upvotes: 4

Related Questions