CODEWITHSUNDEEP
sqlsql-servercase-when
stig
stig

Reputation: 1220

SQL Multiple Case When and mulitple results

I have a sql query that simplified looks like this:

declare @Method varchar(max);
set @Method = 'phone'

select
case 
    when @Method = 'phone' and u.PhoneNumber is not null and u.PhoneNumberConfirmed = 1 then u.PhoneNumber
    when @Method = 'email' and u.Email is not null and u.EmailConfirmed = 1 then u.Email
end,

case 
    when @Method = 'phone' and u.PhoneNumberSecondary is not null and u.PhoneNumberSecondaryConfirmed = 1 then u.PhoneNumberSecondary
    when @Method = 'email' and u.EmailSecondary is not null and u.EmailSecondaryConfirmed = 1 then u.EmailSecondary
 end

So this displays it in 2 columns. I want all the results in only 1 column. I could split it up with union like this:

...
select
case 
    when @Method = 'phone' and u.PhoneNumber is not null and u.PhoneNumberConfirmed = 1 then u.PhoneNumber
    when @Method = 'email' and u.Email is not null and u.EmailConfirmed = 1 then u.Email
end 

from
...

union

select

case 
    when @Method = 'phone' and u.PhoneNumberSecondary is not null and u.PhoneNumberSecondaryConfirmed = 1 then u.PhoneNumberSecondary
    when @Method = 'email' and u.EmailSecondary is not null and u.EmailSecondaryConfirmed = 1 then u.EmailSecondary
 end

But the problem is then that it could be that it doesnt exist any rows with PhoneNumberSecondary and then I get a NULL row.

What is the best way to solve this?

Upvotes: 1

Views: 78

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726539

Using UNION is the right way of doing it. If you need to get rid of NULLs, add a NULL check, for example, to the outer query:

SELECT contact_info
FROM (
select
case 
    when @Method = 'phone' and u.PhoneNumber is not null and u.PhoneNumberConfirmed = 1 then u.PhoneNumber
    when @Method = 'email' and u.Email is not null and u.EmailConfirmed = 1 then u.Email
end as contact_info
from ...
UNION
select
case 
    when @Method = 'phone' and u.PhoneNumberSecondary is not null and u.PhoneNumberSecondaryConfirmed = 1 then u.PhoneNumberSecondary
    when @Method = 'email' and u.EmailSecondary is not null and u.EmailSecondaryConfirmed = 1 then u.EmailSecondary
 end as contact_info
from ...
)
WHERE contact_info IS NOT NULL

Upvotes: 2

Related Questions