select auser.first_name as "TECHNICIAN", max(sd.employeeid) as "Employee ID", longtodate(auser.createdtime) as "Creation Date", STRING_AGG (arole.name, ',') "Current Assigned Role", max(sd.status) "Current Status", max(deptdef.deptname) "Department Name", max(sd.jobtitle) as "Job Title", max(sdorg.name) as "Location"
from aaauser "auser"
inner join portaltechnicians "ptech" on auser.user_id = ptech.userid
left join sduser "sd" on ptech.userid = sd.userid
left join userdepartment "userdept" on sd.userid = userdept.userid
left join departmentdefinition "deptdef" on userdept.deptid = deptdef.deptid
left join sitedefinition "sitedef" on deptdef.siteid = sitedef.siteid
left join sdorganization "sdorg" on sitedef.siteid = sdorg.org_id
left join aaalogin "alog" on auser.user_id= alog.user_id
left join aaaaccount "acc" on alog.login_id = acc.login_id
left join sdauthorizedrole "auth" on acc.account_id = auth.account_id
left join aaarole "arole" on auth.role_id = arole.role_id
group by auser.user_id, auser.FIRST_NAME, auser.createdtime