TSQL - ISNULL over multiple columns

I have a s开发者_如何学运维imple SQL query (SQL Server 2005) where I'm selecting from a table that contains multiple columns that have BIT values. These columns are nullable so can contain NULL, 0 or 1.

There are a fair number of these columns and in my query I want to return zero if the value is NULL. I'm currently using ISNULL like so:

SELECT Name, Age, ISNULL(LikesOranges,0), ISNULL(LikesApples,0), ISNULL(LikesPears,0)
FROM FoodPreferences

As I've mentioned, there are a lot of these BIT columns (much more than in the simple example above). Is there a way I can use ISNULL over multiple columns like this:

SELECT ISNULL(*,0) FROM FoodPreferences

The above query doesn't work but you get what I'm trying to do - so I can avoid having to write an ISNULL statement for each column,

Thanks.

Try this:

SELECT COALESCE(LikesOranges, LikesApples, LikesPears) AS MyBit FROM FoodPreferences

This will return the first non-null value. If all fields are NULL the result is NULL.

UPDATE:

And the conclusion is:

SELECT ISNULL(COALESCE(LikesOranges, LikesApples, LikesPears),0) AS MyBit FROM FoodPreferences

so I can avoid having to write an ISNULL statement for each column,

Run this query and copy the result to your select statement. system_type_id = 104 filters the result on bit columns.

select stuff((select  ', isnull('+name+', 0)'
              from sys.columns
              where object_id = object_id('FoodPreferences') and 
                    system_type_id = 104
              for xml path('')), 1, 1, '')

Result:

-------------------------------------------------------------------------
 isnull(LikesOranges, 0), isnull(LikesApples, 0), isnull(LikesPears, 0)

I don't think so. But an option might be to create a view onto that table and put all the ISNULL statements in the view. At least then you won't have to do it every time

eg.

CREATE VIEW vwFoodPreferences
AS
SELECT Name, 
       Age, 
       ISNULL(LikesOranges,0) AS LikesOranges, 
       ISNULL(LikesApples,0) AS LikesApples, 
       ISNULL(LikesPears,0) AS LikesPears
FROM   FoodPreferences

Unfortunately, the simple answer is no.

You could write sql dynamically, but whatever happens, the final resulting sql would have to be ISNULL(a,0), ISNULL(b,0), ISNULL(c,0), ISNULL(d,0), etc

i think you can write a simple program and generate select clause by reading all columns and generating the select

while not this :

SELECT COALESCE(LikesOranges, LikesApples, LikesPears, 0) AS MyBit FROM FoodPreferences

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017