How to select different columns depending on values in a column

Question

So, I have a table with the columns staff, associate and Matter type (which is always either set to 'Lit' or 'PSD'.)

When type field = 'Lit' I need to include the Staff field as the staff field in the select statement. When the type field is set to 'PSD' I need to include the associate field as the staff field in the select statement.

I know I can do this as two separate queries, but I cannot figure out how to combine the two into a single query - there's an easy answer, but after not being able to figure it out for a while, I'm just not coming up with the answer.

Ideas?

possible duplicate of How to perform an IF...THEN in an SQL SELECT? — Sruit A.Suk
– Sruit A.Suk, Commented Jun 14, 2015 at 20:54

9ee1 · Accepted Answer · 2015-06-14 20:54:12Z

4

SELECT
    CASE WHEN
        [MatterType] = 'Lit'
    THEN
        [Staff]
    ELSE
        [Associate]
    END AS [NewStaff]
FROM
    MyTable;

This uses an inline case condition in the SELECT list.

answered Jun 14, 2015 at 20:54

9ee1

1,0781 gold badge10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FutbolFan · Accepted Answer · 2015-06-14 21:39:22Z

3

To combine the results of two queries with same number of columns, you can use UNION ALL or UNION. Preferably union all because of less overhead.

SELECT  staff AS staff ,
        mattertype
FROM    my_table
WHERE   mattertype = 'Lit'
UNION ALL
SELECT  associate AS staff ,
        mattertype
FROM    my_table
WHERE   mattertype = 'PSD'

In your case, I would say using CASE is better:

SELECT  CASE WHEN mattertype = 'Lit' THEN staff
             ELSE associate
        END AS staff
       ,mattertype
FROM    my_table

answered Jun 14, 2015 at 21:39

FutbolFan

13.9k3 gold badges27 silver badges35 bronze badges

Comments

jpw · Accepted Answer · 2015-06-14 20:52:17Z

2

If I understand your question right you want either staff or associate as a column called staff depending on the value of matter. If this is the cas you can use a conditional case ... when statement to select the appropriate columns. Something like this:

select matter, case when matter = 'Lit' then staff else associate end as staff from table

As you state that matter has to be either Lit or PSD you only need to check if it is one of the values, otherwise it has to be the other (although you could make the check explicit for clarity).

answered Jun 14, 2015 at 20:52

jpw

45k6 gold badges71 silver badges90 bronze badges

Comments

Turophile · Accepted Answer · 2015-06-15 02:15:14Z

1

The other answers have covered the common & practical, so here is a variation which is sometimes useful. If your staff column is null when [Matter type] = 'PSD' then this would work:

SELECT COALESCE(staff,associate) AS staff
FROM tablename
;

answered Jun 15, 2015 at 2:15

Turophile

3,4151 gold badge15 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to select different columns depending on values in a column

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related