SQL Conditional column data return in a select statement

Question

Here is a simplication of the problem: I have a select that looks like this:

Select ID, Assignee, WorkStream from assignees;

And a snap shot of the data returned looked like this

1|Joe Soap|Internal

2|Mrs Balls|External

What I would like to do is have the select not display the Assignee name if the worksteam is internal. Instead to display the Workstream.

So for example the outcome I want to achieve would be this:

1|Internal|Internal

2|Mrs Balls|External

I hope this makes sense? Basically a conditional select that can detect if a certain column contains a certain value, then replace another columns value with [whatever].

EDIT I want to achieve something like this:

Select ID, if (workstream='internal' select Workstream as Assignee - else - select Assignee as Assigneee), WorkStream from assignees;

score 95 · Accepted Answer · answered Feb 15 '12 at 09:45

95

You didn't mention your DBMS but a searched CASE statement works in all major DBMS's I know off.

SELECT  ID
        , CASE WHEN WorkStream = 'Internal'
               THEN WorkStream
               ELSE Assignee
          END AS Assignee
        , Workstream
FROM    assignees

Reference: MSDN

CASE

Evaluates a list of conditions and returns one of multiple possible result expressions.

answered Feb 15 '12 at 09:45

Lieven Keersmaekers

57,207
13
112
146

1

Is there a way to select multiple columns conditionally? I need to select columns depending on the joined table, otherwise I end up with columns from all tables. – akinuri Nov 21 '19 at 11:01
@akinuri - not sure if I understand your requirement correctly. I'd say, yes offcourse but perhaps better to post an example and expected result. – Lieven Keersmaekers Nov 21 '19 at 17:04
@LievenKeersmaekers My question was about an [attempted solution](https://github.com/akinuri/dump/blob/master/sql/notes/select-columns-conditionally.sql) to a previous [problem](https://stackoverflow.com/questions/58951653/how-to-handle-different-types-of-users-when-loggin-in). – akinuri Nov 22 '19 at 07:54

score 7 · Answer 2 · answered Feb 15 '12 at 09:50

7

SELECT ID, 
       CASE WorkStream  WHEN 'Internal' THEN 'INTERNAL' ELSE Assignee as Assignee,    WorkStream  from assignees

I hope this help.

answered Feb 15 '12 at 09:50

Pongsathon.keng

1,417
11
14

Why is there Workstream between CASE and WHEN? – Steve Scott Nov 22 '21 at 15:31

SQL Conditional column data return in a select statement

2 Answers2

Linked