CODEWITHSUNDEEP
sqlsql-server
img.simone
img.simone

Reputation: 742

SQL Server: SELECT IF conditionally

I have a table with different columns and I need to show different based on the value of a specific column.

My table It's like this:

select col1, col2, col3, col4, col5, col6
from table1

So, for example:

if col1 = '1' --> show col3 and col4.

if col2 = '1' --> show col5 and col6

Upvotes: 1

Views: 85

Answers (2)

onedaywhen
onedaywhen

Reputation: 57093

Rule of thumb 1 : return to the caller the columns used for filtering.

Rule of thumb 2 : don't use union all, it returns duplicates and your result will not be a 'relation' (though in this case there would be no duplicates because col1 is a key but I still think union states the intent best).

select col1, col2, col3 AS colum_a, col4 AS colum_b
from table1
where col1 = 1
union
select col1, col2, col5 AS colum_a, col6 AS colum_b
from table1
where col2 = 1;

Upvotes: 1

jarlh
jarlh

Reputation: 44795

Use case expressions to chose columns to return. Note that a case's return types must be compatible, i.e. col3 and col5, and col4 and col6.

select case when col1 = 1 then col3
            when col2 = 1 then col5
       end,
       case when col1 = 1 then col4
            when col2 = 1 then col6
       end
from tablename

Or, do a UNION ALL:

select col3, col4
from tablename where col1 = 1
union all
select col5, col6
from tablename where col2 = 1

Remaining questions - what do to if both col1 and col2 are 1? Or if none of them are?

Upvotes: 4

Related Questions