SQL Server : case statement

146 Views Asked by At

I have table named boolean which contain 'true' and/or 'false' values as strings in one column.

I have problem to create case statement to show me whether there are only 'true' or 'false' or 'both' values

Example 1:

'true'  
'true'  

result:'true'

Example 2:

'false'   
'false'  
'false' 

Result: 'false'

Example 3:

'true'  
'false'  
'true' 

Result: 'both'

Edit:

case statement should look like:

case 
   when  "column content are only true values"  then 'true' 
   when  "column content are only false values" then 'false'
   else 'both' 
end
3

There are 3 best solutions below

0
On BEST ANSWER

You could aggregate the max and min of the column, and then evaluate the results - if they are the same, there's only one value in the column. If not, there must be both. Note that since these are string representations the values are sorted lexicographically:

SELECT CASE WHEN MAX(col) = MIN(col) THEN MAX(col) ELSE 'both' END
FROM   my_table
1
On
select case when count(distinct bool_column) = 2 then 'both'
            when sum(case when bool_column = 'false' then 1 end) > 0 then 'false'
            else 'true'
       end as result
from your_table
0
On
SELECT CASE WHEN MIN(Col) <> MAX(Col) THEN 
          'Both'
       ELSE
          MIN(Col)
       END
FROM YourTable