How to get multiple columns in a single SQL CASE statement?

Question

I'm trying to get multiple columns(insuredcode, insuredname in this case) from a single CASE statement.

The following query has been tried but it concatenates both insuredcode and insuredname as one column.

What is the correct syntax to return exactly two columns from such CASE statement?

select
    case
        when a.policyno[2] in ('E', 'W') then c.insuredcode || c.insuredname
        else b.insuredcode || b.insuredname
    end
from prpcmain a
left join prpcinsured_1 b on b.proposalno=a.proposalno 
left join prpcinsured_2 c on c.proposalno=a.proposalno
where a.policyno in (select policyno from policyno_t);

Answer 1

A CASE statement can return only single column not multiple columns

You need two different CASE statements to do this

select
    case
        when a.policyno[2] in ('E', 'W') then c.insuredcode 
        else b.insuredcode 
    end as insuredcode ,
    case
        when a.policyno[2] in ('E', 'W') then  c.insuredname
        else b.insuredname
    end as insuredname
from prpcmain a
left join prpcinsured_1 b on b.proposalno=a.proposalno 
left join prpcinsured_2 c on c.proposalno=a.proposalno
where a.policyno in (select policyno from policyno_t);

Answer 2

I can suggest something else that might be slightly faster :

SELECT s.insuredcode,s.insuredname FROM (
    SELECT a.policyno,b.insuredcode,b.insuredname
    FROM prpcmain a
    left join prpcinsured_1 b on b.proposalno=a.proposalno 
    WHERE a.policyno[2] not in ('E', 'W')
    UNION ALL
    SELECT a.policyno,c.insuredcode,c.insuredname
    FROM prpcmain a
    left join prpcinsured_2 c on c.proposalno=a.proposalno 
    WHERE a.policyno[2]  in ('E', 'W')
) s
where s.policyno in (select policyno from policyno_t);

As to your question, @Prdp shows what you need to do.

Answer 3

It is simply like if/else condition in any language, you can define your condition in When statement and if it is true, SQL executes the Then statement, otherwise executes Else part, as described below:

     Select 
                 CASE 
                    WHEN (cs.ResultSubmitToHOD = 1) THEN 'HOD'
                    WHEN (cs.ResultSubmitToExamDep = 1) THEN 'Exam'
                    ELSE 'Teacher'
                END AS ResultSubmitStatus
     From dbo.CourseSection as cs