Version : 13000

DB : MSSQL

OUTPUT :

select auser.first_name as "TECHNICIAN",
(sd.employeeid) as "Employee ID",
longtodate(auser.createdtime) as "Creation Date",
(SELECT STUFF(( Select ar.NAME+ CHAR(10) from PortalTechnicians pu
left join AaaUser au on pu.userid=au.USER_ID
 left join AaaLogin al on au.USER_ID=al.USER_ID
left join AaaAccount aa on al.LOGIN_ID=aa.LOGIN_ID
left join sdAuthorizedRole aar on aa.ACCOUNT_ID=aar.ACCOUNT_ID
left join AaaRole ar on aar.ROLE_ID=ar.ROLE_ID LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID
WHERE pu.userid=ptech.userid and (AaaRoleToCategory.ROLE_ID IS NULL) FOR XML PATH ('')), 1, 0, '')) 'Role1' ,
(sd.status) "Current Status",
(deptdef.deptname) "Department Name",
(sd.jobtitle) as "Job Title",
(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