1
select concat('3-',`rcr`.`TargetID`,'-',`rcr`.`CheckCategoryID`) AS `JoinID`,`rcr`.`TargetID` AS `ClientID`,`rcr`.`CheckCategoryID` AS `CheckCategoryID`,elt(`rcr`.`CheckCategoryID`,'Performance','Stability','Security','Unknown') AS `CheckCategoryName`,`rcr`.`TargetStraightScore` AS `TargetStraightScore`,`rcr`.`TargetWeightedScore` AS `TargetWeightedScore`,max((case when (`sp`.`PropertyName` = 'HowToWeighScores') then `sp`.`Value` end)) AS `ScoringMethod`,max((case when (`sp`.`PropertyName` = 'GreenMin') then (`sp`.`Value` * 100) end)) AS `GreenMin`,max((case when (`sp`.`PropertyName` = 'YellowMin') then (`sp`.`Value` * 100) end)) AS `YellowMin`,max((case when (`sp`.`PropertyName` = 'YellowIsUnhealthy') then `sp`.`Value` end)) AS `YellowIsUnhealthy`,ifnull(max((case when (`sp`.`PropertyName` = 'ReportCheckDisplayCount') then `sp`.`Value` end)),5) AS `ReportCheckDisplayCount` from (`labtech`.`plugin_sap_reportcategoryresults` `rcr` join `labtech`.`plugin_sap_properties` `sp`) where (`rcr`.`TargetType` = 3) group by `rcr`.`TargetID`,`rcr`.`CheckCategoryID`