SELECT serd.Name "Service Category",
       max(list.Templatename) "Template Name",
       STUFF(
               (SELECT ',' + tgm.QUEUENAME
                FROM TemplateToGroupMapping tgm
                WHERE list.TemplateID=tgm.TemplateID
                  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') "Group",
       STUFF(
               (SELECT ',' + ug.NAME
                FROM UserGroups ug
                LEFT JOIN TemplateUGMapping tugm ON ug.ID=tugm.USERGROUPID
                WHERE list.TemplateID=tugm.TemplateID
                  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') "User Group",
       (slad.slaname) "Sla name",
       max(slad.DUEBYDAYS) "DUEBYDAYS",
       max(slad.duebyhours) "duebyhours",
       STUFF(
               (SELECT ',' + (CASE
                                  WHEN EscalateToN.orgroleid ='1' THEN 'DEPHEAD'
                                  WHEN EscalateToN.orgroleid ='2' THEN 'REPORTINGTO'
                                  ELSE aaa1.FIRST_NAME
                              END)
                FROM EscalateToN
                LEFT JOIN aaauser aaa1 ON EscalateToN.USERID=aaa1.USER_ID
                LEFT JOIN orgroles org ON EscalateToN.orgroleid=org.orgroleid
                LEFT JOIN EscalateToMediator ON EscalateToN.ESCALATETOID=EscalateToMediator.ESCALATETOID
                LEFT JOIN ServiceApproverMapping ON EscalateToMediator.ESCALATETOID=ServiceApproverMapping.ESCALATETOID
                WHERE list.TEMPLATEID=ServiceApproverMapping.SERVICETEMPLATEID
                  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') "Approver" FROM RequestTemplate_list list
LEFT JOIN sladefinition slad ON list.slaid=slad.slaid
LEFT JOIN RequestTemplate_fields field ON list.TEMPLATEID=field.TEMPLATEID
LEFT JOIN Aaauser aaa ON list.Createdby=aaa.USER_ID
LEFT JOIN sduser sd ON field.TechnicianID=sd.userid
LEFT JOIN AaaUser aau ON sd.userid=aau.user_id
LEFT JOIN ServiceDefinition serd ON list.PARENT_SERVICE=serd.SERVICEID
GROUP BY serd.Name,
         list.Templateid,
         slad.slaname
ORDER BY 2

SELECT serd.Name "Service Category",
       list.Templatename "Template Name",
       array_to_string(array_agg(tgm.QUEUENAME), ',') "Group",
       array_to_string(array_agg(ug.NAME), ',') "User Group",
       slad.slaname "Sla name",
       max(slad.DUEBYDAYS) "Duedate",
       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 list
LEFT JOIN sladefinition slad ON list.slaid=slad.slaid
LEFT JOIN RequestTemplate_fields field ON list.TEMPLATEID=field.TEMPLATEID
LEFT JOIN Aaauser aaa ON list.Createdby=aaa.USER_ID
LEFT JOIN sduser sd ON field.TechnicianID=sd.userid
LEFT JOIN AaaUser aau ON sd.userid=aau.user_id
LEFT JOIN ServiceDefinition serd ON list.PARENT_SERVICE=serd.SERVICEID
LEFT JOIN TemplateToGroupMapping tgm ON list.TemplateID=tgm.TemplateID
LEFT JOIN TemplateUGMapping tugm ON list.TemplateID=tugm.TemplateID
LEFT JOIN UserGroups ug ON tugm.USERGROUPID=ug.ID
LEFT JOIN ServiceApproverMapping ON list.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
GROUP BY serd.Name,
         list.Templatename,
         slad.slaname
ORDER BY 1