SQL Conditional Case

Question

I have a table listing the items and status about these items. The problem is that some elements have several different state records. 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 for each 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 am trying to do this using the T-SQL Case statements, but cannot figure it out correctly. The conditions are any Pass + anything - this is Pass, Fail + No Results - fail, and Null - not.

Answer 1

Try using the case to convert to ordered results and a group on this, finally, you will need to go back to a pleasant, understandable to the person request:

 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 the CTE instruction to make things a little clearer, but everything can be done in one SELECT , for example:

 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 

Answer 2

You can use Max(Status) with Group by Host to get Distinct values:

 Select host, coalesce(Max(status),'No results') status From Table1 Group by host Order by host 

Script demonstration results:

 | HOST | STATUS | |---------|------------| | 1.1.1.1 | PASS | | 1.1.4.4 | No results | | 1.2.2.2 | FAIL | | 1.2.3.3 | PASS | | 1.4.2.1 | FAIL | 

By default, SQL Server is case-insensitive. If case sensitivity is a problem for your server, then use the lower () function, as shown below:

 Select host, coalesce(Max(Lower(status)),'No results') status From Table1 Group by host Order by host 

Screenshot

Answer 3

 WITH CTE( HOST, STATUSValue) AS( SELECT HOST, CASE STATUS WHEN 'PASS' 1 ELSE 0 END AS StatusValue FROM Data ) SELECT DISTINCT HOST, CASE ISNULL(GOOD.STATUSVALUE,-1) WHEN 1 THEN 'Pass' ELSE CASE ISNULL( BAD.STATUSVALUE,-1) WHEN 0 Then 'Fail' Else 'No Results' END END AS Results FROM DATA AS D LEFT JOIN CTE AS GOOD ON GOOD.HOST = D.HOST AND GOOD.STATUSVALUE = 1 LEFT JOIN CTE AS BAD ON BAD.HOST = BAD.HOST AND BAD.STATUSVALUE = 0