SELECT serd.Name "Service Category",
       reqtl.templatename "Service catalog",
       sla.slaname "SLA" ,
       sdo.name "Site",
       array_to_string(array_agg(CASE
                                     WHEN EscalateToN.orgroleid ='1' THEN 'DEPHEAD'
                                     WHEN EscalateToN.orgroleid ='2' THEN 'REPORTINGTO'
                                     ELSE (aaa1.FIRST_NAME)
                                 END), ',') "Approver" FROM RequestTemplate_list reqtl
LEFT JOIN ServiceDefinition serd ON reqtl.PARENT_SERVICE=serd.SERVICEID
LEFT JOIN SC_TemplateSLAAssociation sct ON sct.template=reqtl.templateid
LEFT JOIN sladefinition sla ON sla.slaid=sct.sla
LEFT JOIN sdorganization sdo ON sdo.org_id=sla.siteid
LEFT JOIN srapproverdetails ON srapproverdetails.id=serd.serviceid
LEFT JOIN aaauser ON aaauser.user_id = srapproverdetails.approver
LEFT JOIN aaacontactinfo ON aaauser.user_id = aaacontactinfo.contactinfo_id
LEFT JOIN sduser sd ON aaauser.user_id=sd.userid
LEFT JOIN userdepartment ud ON aaauser.user_id=ud.userid
LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID
LEFT JOIN SDOrganization sdoa ON dpt.SITEID=sdoa.ORG_ID
LEFT JOIN ServiceApproverMapping ON reqtl.TEMPLATEID=ServiceApproverMapping.SERVICETEMPLATEID
LEFT JOIN EscalateToMediator ON ServiceApproverMapping.ESCALATETOID=EscalateToMediator.ESCALATETOID
LEFT JOIN EscalateToN ON EscalateToMediator.ESCALATETOID=EscalateToN.ESCALATETOID
LEFT JOIN orgroles org ON EscalateToN.orgroleid=org.orgroleid
LEFT JOIN AaaUser aaa1 ON EscalateToN.USERID=aaa1.USER_ID
WHERE reqtl.isdeleted='0'
group by 1,2,3,4
order by 1