问题:

I have table with 1 + 5 columns as follows.

MemberID ==> represents Member_ID

Value1 ==> Represents Value1

Value2 ==> Represents Value2

Value3 ==> Represents Value3

Value4 ==> Represents Value4

Value5 ==> Represents Value5

Only one column is not null at any given time(In the set of Value1, Value2, Value3, Value4 and Value5).

I want to run a query where in I get the result as Member_ID, Value where the value is non null value from any of the 5 Value columns.

How to do that in TSQL? Thank you, Smith

回答1:

select memberID, coalesce(value1, value2, value3, value4, value5)
from myTable

If the possibility exists that all of the values could be null, you may want to default to a value.

select memberID, coalesce(value1, value2, value3, value4, value5, <default>)
from myTable

回答2:

You could try a case statement

select memberID,
       case
           when Value1 is not null then
               Value1
           when Value2 is not null then
               Value2  
           when Value3 is not null then
               Value3
           when Value4 is not null then
               Value4
           when CValue5 is not null then
               Value5
       end as [Value]
from myTable

There is also the possibility to add an "else" to the case statement to return a default if all values were null.