How to show values in Col3 where Col1 values are true and for false values in Col1 show NULL in Col3-CodePudding

SELECT Col1, Col2, Col3
FROM Table

Results Set: ( Sample table )

 Col1        Col2        col3
----------- ---------- -----------
Value       Value      Value
Value       Value      Value         
Value       Value      Value         
Value       Value      Value         
Value       Value      Value

Show Col3 with NULL/empty values ( assume that Col3 supports NULL if needed ) EXCEPT for the rows(=values) where col1 condition is true keep the values on Col2 for all rows.

Condition as in ( WHERE Col > 2| WHERE chareindex( 'x' , Col1 ) Etc.. )

Table end results: ( The conditions in here are true for rows 2 and 5 )

  Col1        Col2        col3
----------- ---------- -----------
1 Value       Value      NULL
2 Value       Value      Value         
3 Value       Value      NULL         
4 Value       Value      NULL         
5 Value       Value      Value

More expressed way for the question :

SELECT EmployeeID, Firstname, Lastname,
From Employees

Results set:

EmployeeID  FirstName  LastName
----------- ---------- --------------------
1           Nancy      Davolio
2           Andrew     Fuller
3           Janet      Leverling
4           Margaret   Peacock
5           Steven     Buchanan
6           Michael    Suyama
7           Robert     King

lets say that in the code above there's a condition for employeeID and employeeID 3 and 6 was true to that condition

I'm looking to achieve:

EmployeeID  FirstName  LastName
----------- ---------- --------------------
1           Nancy      NULL
2           Andrew     NULL
3           Janet      Leverling
4           Margaret   NULL
5           Steven     NULL
6           Michael    Suyama
7           Robert     NULL

what condition/s and how should they be put to achieve this results set

You may change the "base code" completely
You don't know the values on 'Lastname' col ( or col3 )
You must keep all the rows and columns value for the false conditions values
The table is big

Another way of putting the question: ( based on the first paragraph of the question)

For a row which the value in col1 is true to a condition/s, show the value in Col3, if not show NULL/empty and keep the col2 values for all rows.

CodePudding user response：

Try this:

Select col1, col2, 
Case When col1 (condition) Then col3 Else null End As col3 
From Table

CodePudding user response：

I'm not sure to have understood excatly what you want to do, but does SELECT CASE WHEN could solve your problem ?

Here's an example :

select 
    Col1, 
    Col2,
    case when (Col1 > 20) then NULL else Col3 end as Col3
FROM Table

It would give you the following :

 Col1        Col2        col3
----------- ---------- -----------
5           Value      Value
21          Value      NULL         
7           Value      Value         
8           Value      Value         
40          Value      NULL

Using this way, you conditionnaly select data from the column or NULL

EDIT : concerning the explanation of the case...when, you can find explanations & examples here : https://www.w3schools.com/sql/sql_case.asp