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

Thanks in advance!

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; 

Answer 1

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.

Answer 2

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

I hope this help.