1

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?

jpw
  • 44,361
  • 6
  • 66
  • 86
Jeff Stouse
  • 11
  • 2
  • possible duplicate of [How to perform an IF...THEN in an SQL SELECT?](http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select) – Sruit A.Suk Jun 14 '15 at 20:54

4 Answers4

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.

9ee1
  • 1,078
  • 1
  • 10
  • 25
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
FutbolFan
  • 13,235
  • 3
  • 23
  • 35
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).

jpw
  • 44,361
  • 6
  • 66
  • 86
0

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
;
Turophile
  • 3,367
  • 1
  • 13
  • 21