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

Question

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

The following query is awakened, but it combines both insuredcode and insuredname as one column.

What is the correct syntax for returning exactly two columns from such a CASE expression?

 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

Operator

A CASE can only return one column, not multiple columns

For this you need two different CASE statements.

 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 could be a little 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 for your question, @Prdp shows what you need to do.

Answer 3

This is just an if / else condition in any language, you can define your condition in the When statement, and if it is true, SQL executes the Then statement, otherwise the Else part executes, 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