SQL Conditional Case

case, distinct-values, sql, sql-server, t-sql

Solution

Try using a `case` statement to convert to ordered results and group on that, finally, you'll need to convert back to the nice, human-readable answer:

with cte1 as (
  SELECT HOST,
         [statNum] = case
                      when Status like 'PASS' then 2
                      when Status like 'FAIL' then 1
                      else 0
                    end
  FROM table
)
SELECT HOST, case max(statNum) when 2 then 'PASS' when 1 then 'FAIL' else 'No Results' end
FROM cte1
GROUP BY HOST

NOTE: I used a CTE statement to hopefully make things a little clearer, but everything could be done in a single `SELECT`, like so:

SELECT HOST,
 [Status] = case max(case when Status like 'PASS' then 2 when Status like 'FAIL' then 1 else 0 end)
    when 2 then 'PASS'
    when 1 then 'FAIL'
    else 'No Result'
   end
FROM table

Problem

I have a table that lists items and a status about these items. The problem is that some items have multiple different status entries. For example. ``` HOST Status 1.1.1.1 PASS 1.1.1.1 FAIL 1.2.2.2 FAIL 1.2.3.3 PASS 1.4.2.1 FAIL 1.4.2.1 FAIL 1.1.4.4 NULL ``` I need to return one status per asset. ``` HOST Status 1.1.1.1 PASS 1.2.2.2 FAIL 1.2.3.3 PASS 1.4.2.1 FAIL 1.1.4.4 No Results ``` I have been trying to do this with T-SQL Case statements but can't quite get it right. The conditions are any Pass + anything is a Pass, Fail+ No Results is a fail and Null is No Results.

Original source