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);

duplicate: https://stackoverflow.com/questions/2072721/how-can-i-select-multiple-columns-within-a-case-when-on-sql-server — AJ AJ, Aug 02 '18 at 12:27

score 32 · Accepted Answer · answered Oct 09 '16 at 09:13

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);

any ideas if the query engine optimizes something if conditions are same in both cases — PirateApp, Jun 05 '18 at 04:45

score 5 · Answer 2 · answered Nov 30 '17 at 10:03

5

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

answered Nov 30 '17 at 10:03

Luqman Cheema

409
6
5

2

This does not answer the question, which is not only asking for yielding two columns, but also has a specified SQL dialect: Informix. – Lorinc Nyitrai Nov 30 '17 at 10:23
3

I think @luqman-cheema just misunderstood the question, as frankly I did too. I came here looking for this answer, actually :) So, the original question above wants two columns IN THE RESULT, and this answer is talking about using two columns within the CASE IN THE QUERY... – Predrag Stojadinović Feb 27 '18 at 12:07
3

Exactly what I was looking for, thanks ! (even though someone voted you down) – Mike Gledhill Dec 07 '18 at 08:11
1

Exactly what I was looking for as well! – Chique_Code Oct 21 '20 at 12:38

score 4 · Answer 3 · edited May 23 '17 at 12:19

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.

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

3 Answers3