I have got a table with columns in the following format where host_names are repeated and a single host can have both Compliant and Non-Compliant values against it. How can i write a query which checks for each host_name and marks it as Non-Compliant if any of its rows has Non-Compliant.
compliance host_name
Compliant Host1
Non-Compliant Host1
Compliant Host2
Non-Compliant Host3
Compliant Host4
For ex: in the above table, Host1 has both Compliant and Non-Compliant values in two of its rows. Since one of the value is non-compliant, i want to take that host once and create a table in following format.
compliance host_name
Non-Compliant Host1
Compliant Host1
Non-Compliant Host3
Compliant Host4