SELECT qd.QUEUENAME "Support group",
       ci.DESCRIPTION "Description",
       su.first_name "Owned By",
       STUFF(
               (SELECT ',' + au.first_name
                FROM queue_technician qt
                LEFT JOIN sduser sd ON qt.technicianid=sd.userid
                LEFT JOIN aaauser au ON sd.userid=au.user_id
                WHERE qt.queueid=qd.queueid
                  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS "Interested Technicians",
       STUFF(
               (SELECT ',' + au.first_name
                FROM queueescalation qe
                LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
                LEFT JOIN sduser sd ON esc.userid=sd.userid
                LEFT JOIN aaauser au ON sd.userid=au.user_id
                WHERE qe.category='NOTIFY_REQ_ADD_IN_QUEUE'
                  AND qe.queueid=qd.queueid
                  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS "Notify when new request is added",
       STUFF(
               (SELECT ',' + au.first_name
                FROM queueescalation qe
                LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
                LEFT JOIN sduser sd ON esc.userid=sd.userid
                LEFT JOIN aaauser au ON sd.userid=au.user_id
                WHERE qe.category='NOTIFY_UNPICK_REQ_IN_QUEUE'
                  AND qe.queueid=qd.queueid
                  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS "Notify when request is left unpicked",
       STUFF(
               (SELECT ',' + au.first_name
                FROM queueescalation qe
                LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
                LEFT JOIN sduser sd ON esc.userid=sd.userid
                LEFT JOIN aaauser au ON sd.userid=au.user_id
                WHERE qe.category='NOTIFY_REQ_EDIT_IN_QUEUE'
                  AND qe.queueid=qd.queueid
                  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS "Notify when request is updated",
       qe.email "Group E-Mail",
       qd.sendername "Sender's Name",
       qd.replyaddress "Sender's e-mail",
       sdo.name "Site Name" FROM QueueDefinition qd
LEFT JOIN Queue_email qe ON qe.queueid=qd.queueid
LEFT JOIN ci ci ON qd.ciid=ci.ciid
LEFT JOIN SupportGroup sg ON sg.ciid=qd.ciid
LEFT JOIN AaaUser su ON sg.ownedby=su.user_id
LEFT JOIN SiteDefinition site ON qd.siteid=site.siteid
LEFT JOIN SDOrganization sdo ON sdo.org_id=site.siteid

SELECT qd.QUEUENAME "Support Group",
       ci.DESCRIPTION "Description",
       su.first_name "Owned By",
       sdo.name "Site Name",

  (SELECT array_to_string(array_agg(au.first_name), ',')
   FROM queue_technician qt
   LEFT JOIN sduser sd ON qt.technicianid=sd.userid
   LEFT JOIN aaauser au ON sd.userid=au.user_id
   WHERE qt.queueid=qd.queueid) "Interested Technicians",

  (SELECT array_to_string(array_agg(au.first_name), ',')
   FROM queueescalation qe
   LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
   LEFT JOIN sduser sd ON esc.userid=sd.userid
   LEFT JOIN aaauser au ON sd.userid=au.user_id
   WHERE qe.category='NOTIFY_REQ_ADD_IN_QUEUE'
     AND qe.queueid=qd.queueid) "Notify when new request is added",

  (SELECT array_to_string(array_agg(au.first_name), ',')
   FROM queueescalation qe
   LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
   LEFT JOIN sduser sd ON esc.userid=sd.userid
   LEFT JOIN aaauser au ON sd.userid=au.user_id
   WHERE qe.category='NOTIFY_UNPICK_REQ_IN_QUEUE'
     AND qe.queueid=qd.queueid) "Notify when request is left unpicked",

  (SELECT array_to_string(array_agg(au.first_name), ',')
   FROM queueescalation qe
   LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
   LEFT JOIN sduser sd ON esc.userid=sd.userid
   LEFT JOIN aaauser au ON sd.userid=au.user_id
   WHERE qe.category='NOTIFY_REQ_EDIT_IN_QUEUE'
     AND qe.queueid=qd.queueid) AS "Notify when request is updated",
       qe.email "Group E-Mail",
       qd.sendername "Sender's Name",
       qd.replyaddress "Sender's e-mail" FROM QueueDefinition qd
LEFT JOIN Queue_email qe ON qe.queueid=qd.queueid
LEFT JOIN ci ci ON qd.ciid=ci.ciid
LEFT JOIN SupportGroup sg ON sg.ciid=qd.ciid
LEFT JOIN AaaUser su ON sg.ownedby=su.user_id
LEFT JOIN SiteDefinition site ON qd.siteid=site.siteid
LEFT JOIN SDOrganization sdo ON sdo.org_id=site.siteid
ORDER BY 1