SELECT data with CASE statement

Question

I am currently trying to formulate a query to determine if certain software applications are installed on the specified host. Our table in our database was configured in such a way that there are columns containing the host name and software, so, for example, there may be 10 Host A records showing 10 separate pieces of software found on it. ex. Below

ID   Host          SW        
--------------------------
1    Host A        SW A
1    Host A        SW B     
1    Host A        SW C
2    Host B        SW B
2    Host B        SW C
3    Host C        SW F

What I'm trying to find is all the hosts on which software A (Flag Y / N) and software B (flag Y / N) are installed, and format the results so that (using the example above) returns .. .

Expected Results    

Host          SWA_Installed      SWB_Installed
----------------------------------------------
Host A        Y                  Y
Host B        N                  Y
Host C        N                  N

The query that I have below is what I still have (pay attention to the actual DB code, these are the actual SW names) ...

SELECT DISTINCT
 HOST.a_display_label AS HOST,
 CASE 
  WHEN 
   sof.a_display_label = 'HPS' THEN 'Y' 
  ELSE 'N' 
 END AS HPS_INSTALLED,
 CASE 
  WHEN 
   sof.a_display_label = 'PowerToken' THEN 'Y' 
  ELSE 'N' 
 END AS PT_INSTALLED
FROM vcms.node_1 HOST
  JOIN vcms.installed_software_1 sof
    ON LOWER (sof.a_root_container) = LOWER (HOST.cmdb_id)

, , DISTINCT, - . :

Query Results

Host          SWA_Installed      SWB_Installed
----------------------------------------------
Host A        Y                  N
Host A        N                  Y
Host A        N                  N
Host B        N                  N
Host B        N                  Y
Host C        N                  N

, " ". .

Answer 1

:

select h.a_display_label as host,
       max(case when s.a_display_label = 'HPS' then 'Y' else 'N'
           end) as hps_installed,
       max(case when s.a_display_label = 'PowerToken' then 'Y' else 'N'
           end) as pt_installed
from vcms.node_1 h join
     vcms.installed_software_1 s
     on lower(s.a_root_container) = lower(h.cmdb_id)
group by h.a_display_label;

. , 'Y' > 'N', min() max(). "1" "" "0" , .

, SELECT DISTINCT . GROUP BY, SELECT DISTINCT .

Answer 2

Host. max min case, , case .

SELECT 
 HOST.a_display_label AS HOST,
 MAX(CASE 
  WHEN 
   sof.a_display_label = 'HPS' THEN 'Y' 
  ELSE 'N' 
 END) AS HPS_INSTALLED,
 MAX(CASE 
  WHEN 
   sof.a_display_label = 'PowerToken' THEN 'Y' 
  ELSE 'N' 
 END) AS PT_INSTALLED
FROM vcms.node_1 HOST
  JOIN vcms.installed_software_1 sof
    ON LOWER (sof.a_root_container) = LOWER (HOST.cmdb_id)
GROUP BY HOST.a_display_label

, a_display_label join. , .

Answer 3

I just understand that mine is not as effective as another answer, but for reference only.

Please note that this is sql server, but the same work for oracle

SQL FIDDLE DEMO

SELECT  [Host],
        CASE WHEN 
             COUNT( CASE WHEN [SW] = 'SW A' THEN 1 END)  >= 1 THEN 'YES' 
                                                              ELSE 'NO' 
        END as sw_a,
        CASE WHEN 
             COUNT( CASE WHEN [SW] = 'SW B' THEN 1 END)  >= 1 THEN 'YES' 
                                                              ELSE 'NO' 
        END as sw_b

FROM hosts
GROUP BY [Host]

OUTPUT