tsql : is it possible to do nested case statements in a select?
26,574
Solution 1
You could simplify this with COALESCE.
SELECT TOP 10 id, COALESCE(x.boy, x.girl, x.dog, x.cat) as Who
FROM house x
Solution 2
Yes. There is nothing wrong with a case within a case.
Although, here is your script, written corectly:
SELECT top 10
id,
case
when x.boy IS NOT NULL then x.boy
else case
when x.girl IS NOT NULL THEN x.girl
else case
when x.dog IS NOT NULL THEN x.dog
else x.cat
end
end
end as Who
from house x
OR
SELECT top 10
id,
case
when x.boy IS NOT NULL then x.boy
when x.girl IS NOT NULL THEN x.girl
when x.dog IS NOT NULL THEN x.dog
else x.cat
end as Who
from house x
OR
SELECT top 10
id,
coalesce(x.boy, x.girl, x.dog, x.cat) AS Who
from house x
Author by
phill
Programming server admin stuff with vbscript, python, powershell, php, and c#.
Updated on August 05, 2022Comments
-
phill over 1 year
how do i incorporate a nested if statement in a select clause of a sql query? I know to use case when condition then X else y end but how do you do a nested one in the same fashion for each record in a record set.
if x.boy is not null then x.boy else if x.girl is not null then x.girl else if x.dog is not null then x.dog else x.cat
here is my attempt:
SELECT top 10 id, case when x.boy <> NULL then x.boy else case when x.girl <> NULL x.girl else case when x.dog <> NULL x.dog else x.cat end as Who from house x
is this correct?
-
Gabriel McAdams over 13 yearsMy answer has a few different ways to write your statement that are correct. See those and add your comments