Select Distinct
pppo_cName As 員工姓名,
IsNull(
Stuff((Select '/' + IsNull(Rtrim(calculateChief.pppo_cName),'') + calculateChief.pppo_cJobNumber
From PPWWorkGroup_ppwg calculateChiefGroup
Inner Join PPWPerson_pppo calculateChief on calculateChief.pppo_iID = calculateChiefGroup.ppwg_iCalculateChiefID
And calculateChief.pppo_iRoleID=5 AND calculateChief.pppo_iState=1
Where workGroup.ppwg_iID = calculateChiefGroup.ppwg_iID --與外面的配置是哪個
group by calculateChief.pppo_cName, calculateChief.pppo_cJobNumber for xml path('')), 1, 1, '')
,'') As 員工所屬科長,
IsNull(SupervisorJoinString,'') As 員工所屬督導
From PPWPerson_pppo with(nolock)
Left Join PPWPersonRole_pppr With(nolock) On pppr_iID=pppo_iRoleID
Left Join PPWFloor_ppfl With(nolock) On ppfl_iID=pppo_iFloor
Left Join PPWArea_ppar With(nolock) On ppar_iID=pppo_iAreaID
Left Join PPWWorkGroup_ppwg workGroup With(nolock) On ppwg_iID=pppo_iWorkGroupID
Left Join
(
--根據組串埋全部督導
SELECT Distinct
groupManager.ppwm_iWorkGroupID AS 'GroupID',
STUFF((Select ';'+ IsNull(pppo_cName,'') + pppo_cJobNumber
From PPWWorkGroupManager_ppwm t
Inner Join PPWPerson_pppo on pppo_iID = t.ppwm_iPersonID
Where t.ppwm_iWorkGroupID = groupManager.ppwm_iWorkGroupID --與外面的配置是哪個
And pppo_iRoleID=4 AND pppo_iState=1
--order by ppwm_iWorkGroupID
group by pppo_cName, pppo_cJobNumber
for xml path('')), 1, 1, '') As 'SupervisorJoinString'
From PPWWorkGroupManager_ppwm groupManager
)BelongToSupervisor On BelongToSupervisor.GroupID = ppwg_iID
where 1 = 1 And len(SupervisorJoinString)>10
