Sql Server 2000: Return "true" or "false" based on any 1 of 25 columns being "true"

Question

Hi,

I have to create a query that checks across several different columns, and if any of them have a 1, I want to return true.

Ideal output would be along the lines of:

ID:55
Name:John Doe
IsDealerType1:True
IsDealerType2:True
IsDealerType3:False
IsDealerType4:False
IsDealerType5:True

The problem is, instead of those 5 dealer columns, I have about 20 columns named 1a, 1b, 1c, 1d, etc. If any of the "1" columns is true, then IsDealerType1 should be true.

I'm trying to avoid writing something in the VB.NET code to check each and every column, just because that sheer ugliness should be easy to avoid in SQL - if only I knew how to do it - but I'm not sure how to construct the query. I've been trying stuff like...

SELECT id, 
      name, 
      (1a or 1b or 1c or 1d) as IsDealerType1, 
      (2a or 2b or 2c or 2d) as IsDealerType2 
where id = 55

... but obviously, I'm not doing it right.

Any help is appreciated. Thanks!

Answer 1

CASE WHEN (1a + 1b + 1c + 1d) > 0 THEN 1 ELSE 0 END as IsDealerType1

Answer 2

I like Russel's, but I'm gonna add this as well:

CASE WHEN 1 IN (1a,1b,1c,1d) THEN 1 ELSE 0 END As IsDealerType1

Answer 3

In SQL the BIT types cannot be used in boolean expressions (d'oh!!), they need to be compared with ints:

SELECT id, name, 
   cast(
    case when ([1a]=1 or [1b]=1 or [1c]=1 or [1d]=1) then 1 else 0 end
    as bit) as IsDealerType1,
    cast(case when ([2a]=1 or [2b]=1 or [2c]=1 or [2d]=1) then 1 else 0 end
    as bit) as IsDealerType2 
from [table]
where id = 55

Answer 4

Use the SQL bitwise OR operator. Avoids comparisons and casts.

Example: Joel's answers passes integer 1 or 0 to the client, where you'd arguably expect bit (boolean). Remus' answers needs casts and comparisons.

SELECT id, 
      name, 
      (1a | 1b | 1c | 1d) as IsDealerType1, 
      (2a | 2b | 2c | 2d) as IsDealerType2 
where id = 55