SELECT serd.Name AS "Service Category",
list.Templatename AS "Template Name",
(SELECT STUFF(
  (
        SELECT ',' + tgm.QUEUENAME FROM TemplateToGroupMapping tgm 
        LEFT JOIN RequestTemplate_List rtl1 ON rtl1.templateid = tgm.templateid 
        WHERE rtl1.templateid = rtl2.templateid    FOR XML PATH('')
  ), 1, 1, '')  from RequestTemplate_List rtl2
where rtl2.templateid = list.templateid) AS "Associated Support group",
(SELECT STUFF(
  (
        SELECT ',' + ug.name FROM usergroups ug
        LEFT JOIN templateugmapping tugm on tugm.usergroupid = ug.id
        LEFT JOIN RequestTemplate_List rtl1 ON rtl1.templateid = tugm.templateid 
        WHERE rtl1.templateid = rtl2.templateid    FOR XML PATH('')
  ), 1, 1, '')  from RequestTemplate_List rtl2
where rtl2.templateid = list.templateid) AS "Associated User group",
slad.slaname "Sla name",
(SELECT STUFF(
  ( SELECT ',' + case when etn.orgroleid ='1' then 'DEPHEAD' when  etn.orgroleid ='2' then 'REPORTINGTO' else
(aaa1.FIRST_NAME) end FROM EscalateToN etn
LEFT JOIN AaaUser aaa1 ON etn.USERID=aaa1.USER_ID 
LEFT JOIN EscalateToMediator etm ON etm.ESCALATETOID=etn.ESCALATETOID
LEFT JOIN ServiceApproverMapping sam ON  sam.ESCALATETOID=etm.ESCALATETOID
LEFT JOIN RequestTemplate_List rtl1 ON rtl1.TEMPLATEID=sam.SERVICETEMPLATEID
WHERE rtl1.templateid = rtl2.templateid    FOR XML PATH('')
  ), 1, 1, '')  from RequestTemplate_List rtl2
where rtl2.templateid = list.templateid) AS "Approver",
max(slad.DUEBYDAYS) "Duedate" FROM RequestTemplate_list list
left join SC_TEMPLATESLAASSOCIATION sla1 on list.templateid=sla1.template
left join sladefinition slad on sla1.sla=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.Templatename ,slad.slaname ,list.templateid 
ORDER BY 1

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 SC_TEMPLATESLAASSOCIATION sla1 on list.templateid=sla1.template
left join sladefinition slad on sla1.sla=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 AaaUser aaa1 ON EscalateToN.USERID=aaa1.USER_ID 
group by serd.Name,list.Templatename ,slad.slaname
ORDER BY 1