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;

Lieven Keersmaekers · Accepted Answer · 2012-02-15 09:45:19Z

101

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, 2012 at 9:45

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

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

3 Comments

akinuri Over a year ago

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.

Lieven Keersmaekers Over a year ago

@akinuri - not sure if I understand your requirement correctly. I'd say, yes offcourse but perhaps better to post an example and expected result.

akinuri Over a year ago

@LievenKeersmaekers My question was about an attempted solution to a previous problem.

Pongsathon.keng · Accepted Answer · 2012-02-15 09:50:22Z

7

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

I hope this help.

answered Feb 15, 2012 at 9:50

Pongsathon.keng

1,44511 silver badges15 bronze badges

2 Comments

Steve Scott Over a year ago

Why is there Workstream between CASE and WHEN?

Lukasavicus Over a year ago

@SteveScott This is a valid syntax in which all the 'WHEN' statements are evaluated with the equal ('=') operator. Refs: 1, 2 and 3

Collectives™ on Stack Overflow

SQL Conditional column data return in a select statement

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related