Choosing only rows where one of the columns has a value

user_14966938 0
Viewed 34

I have table.

ID    Number           Param1        Param2        Param3          Param4        Param 5
1      null             null          null          null            null         XTO10
2      null             null          null          KMC3            null         null
3      null             YUP           null          null            null         null
4      103              VB0           BJ0           KL9             null         null
5      null             FH1           null          null            null         null
6      103              VB0           BJ0           KL9             null         null
7      103             null           null          KL9             null         null
8      103             VB0            BJ0           KL9             AS1           AM
9      null            VB0            BJ0           KL9             AS1           AM
10     99              HS1            null          null            AS1           AM

How get?

Choosing only rows where one of the columns has a value. Only one value.

ID    Number           Param1        Param2         Param3          Param4       Param 5
1      null             null          null          null            null         XTO10
2      null             null          null          KMC3            null         null
3      null             YUP           null          null            null         null
5      null             FH1           null          null            null         null
sql-server sql
1 Answers

You can test the sum of each column being not null and return only rows where the total is 1

select *
from Table
where
Iif(number is null,0,1) +
Iif(param1 is null,0,1) +
Iif(param2 is null,0,1) +
Iif(param3 is null,0,1) +
Iif(param4 is null,0,1) +
Iif(param5 is null,0,1) =1

Fiddle here

Alternative using case

select *
from t
where
case when number is null then 0 else 1 end +
case when param1 is null then 0 else 1 end +
case when param2 is null then 0 else 1 end +
case when param3 is null then 0 else 1 end +
case when param4 is null then 0 else 1 end +
case when param5 is null then 0 else 1 end=1
user_153326500
Related