CODEWITHSUNDEEP
sqlsqlite
igigi
igigi

Reputation: 45

Select different column based on condition (CASE?) and get the column name the value is coming from

I have a row with this data:

id=1, p1=X, p2=Y, p1f=0, p2f=NULL

How to select either p1 or p2 based on which p1f or p2f is 0?

I tired:

SELECT 
    id, p1, p2,
    CASE
       WHEN p1f = 0 THEN id, p1
       WHEN p2f = 0 THEN id, p2
       ELSE NULL 
    END 
FROM match

Thank you in advance

UPDATE

I got the value. Thank you!

...however, I still need to know which column the value is coming from.

i.e. must know whether the column p1 or p1 was selected.

Can this be done in one query at all?

Upvotes: 3

Views: 216

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726569

You have to move id out of the case:

SELECT
    id
,   CASE WHEN p1f=0 THEN p1 WHEN p2f=0 then p2 ELSE NULL END as p
,   CASE WHEN p1f=0 THEN 'p1' WHEN p2f=0 then 'p2' ELSE NULL END as p_name
FROM match

This assumes that the type of p1 and p2 is the same. If the two columns have different types, you need to add a cast of one or both to some common type.

Note that if you wanted more columns to be selected based on the same condition you would have to repeat the condition in multiple separate CASEs.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You are quite close:

SELECT id,
       (CASE WHEN p1f = 0 THEN p1
             WHEN p2f = 0 THEN p2
        END)
FROM match;

A case expression only returns one value.

Upvotes: 0

Related Questions