CODEWITHSUNDEEP
sqlsql-server
Jeff Stouse
Jeff Stouse

Reputation: 11

How to select different columns depending on values in a column

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?

Upvotes: 1

Views: 1147

Answers (4)

Turophile
Turophile

Reputation: 3405

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
;

Upvotes: 1

FutbolFan
FutbolFan

Reputation: 13763

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

Upvotes: 3

9ee1
9ee1

Reputation: 1078

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

This uses an inline case condition in the SELECT list.

Upvotes: 4

jpw
jpw

Reputation: 44921

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

Upvotes: 2

Related Questions