How do I Output into Self Specified Data like Yes or No into Self Specified Columns Based on the result of a Query SQL Server 2008

El Amen Published at Dev

El Amen

I have got these data in these 2 tables in SQL Server 2008:

Facility table:

FacilityID      Region          FacilityName           FacilityType
--------------------------------------------------------------------
A-1             Acc            Amen Clinic               Clinic
UW-2            Upp W          Mah Hospital              Gov Hosp
UE-3            Upp E          Mat                       Pr Hosp
A-4             Acc            Dent OPD                  CHPS
BA-5            Br Ah          Class Compl               Gov Hosp

Equipment table:

EquipmentName           FacilityID      
-------------------------------------
BP Apparatus            A-1
Thermometer             A-1
Screen                  A-1
Beds                    A-1
BP Apparatus            UW-2
Thermometer             UW-2
Beds                    UW-2
Vans                    UW-2
Thermometer             UE-3
Screen                  UE-3
BP Apparatus            A-4
Thermometer             A-4

This is my desired output:

acilityID      Region          FacilityName       Bp Apparatus     Thermometer         Screen
-------------------------------------------------------------------------------------------------
A-1             Acc            Amen Clinic        Yes               Yes                 Yes
UW-2            Upp W          Mah Hospital       Yes               Yes                 No
UE-3            Upp E          Mat                No                Yes                 Yes
A-4             Acc            Dent OPD           Yes               Yes                 No
BA-5            Br Ah          Class Compl        No                No                  No

symbiont

the hard part is that 'Bp Apparatus', 'Thermometer' and 'Screen' are not actual columns:

select FacilityID
     , Region
     , FacilityName
     , (case when exists (select * 
                          from Equipment as e 
                          where     e.FacilityID = f.FacilityID 
                                and e.EquipmentName = 'BP Apparatus') 
             then 'Yes' 
             else 'No' 
        end)
       as 'Bp Apparatus'
     , (case when exists (select * 
                          from Equipment as e 
                          where     e.FacilityID = f.FacilityID 
                                and e.EquipmentName = 'Thermometer')
             then 'Yes'
             else 'No'
        end)
       as 'Thermometer'
     , (case when exists (select * 
                          from Equipment as e 
                          where     e.FacilityID = f.FacilityID 
                                and e.EquipmentName = 'Screen')
             then 'Yes'
             else 'No'
        end)
       as 'Screen'
from Facility as f

Collected from the Internet

Please contact [email protected] to delete if infringement.