如何在复杂SQL查询中正确实现ContactCd聚合，避免生成新行？

我需要在现有多表关联的复杂查询中实现ContactCd的聚合，让每个ContactCd不会生成新行。但我不确定如何简化示例，怕简化后丢失查询必需的关联逻辑。

现有查询语句 #

SELECT        
jobNew_HeaderFile.JobCd, 
GRP = max( case when jobCtrl_Rollup.RollupCatCd = 'GRP' then jobCtrl_Rollup.RollupCd end ), 
REG = max( case when jobCtrl_Rollup.RollupCatCd = 'REG' then jobCtrl_Rollup.RollupCd end ), 
' ' AS 'JOB_1', 
' ' AS 'FIELD OPS MANAGER', 
' ' AS 'NATIONAL OPS MANAGER', 
DIV = max( case when jobCtrl_Rollup.RollupCatCd = 'DIV' then jobCtrl_Rollup.RollupCd end ), 
jobCtrl_Company.Company, 
jobNew_HeaderFile.JobName AS LOCATION, 
jobNew_SiteDetail.JobAddr1 AS Addr1, 
jobNew_SiteDetail.JobAddr2 AS Addr2, 
jobNew_SiteDetail.JobCity AS City, 
jobNew_SiteDetail.JobState AS State, 
jobNew_SiteDetail.JobZip1 AS Zip, 
jobNew_SiteDetail_Contacts.ContactCd,
jobNew_SiteDetail_Contacts.FirstName AS FNAME1, 
jobNew_SiteDetail_Contacts.LastName AS LNAME1, 
jobNew_SiteDetail_Contacts.Title AS TITLE1, 
jobNew_SiteDetail_Contacts.FirstName AS FNAME2, 
jobNew_SiteDetail_Contacts.LastName AS LNAME2, 
jobNew_SiteDetail_Contacts.Title AS TITLE2,
jobNew_SiteDetail_Contacts.FirstName AS FNAME3, 
jobNew_SiteDetail_Contacts.LastName AS LNAME3, 
jobNew_SiteDetail_Contacts.Title AS TITLE3,
jobNew_SiteDetail_Contacts.Phone_wk, 
jobNew_SiteDetail_Contacts.Phone_cell, 
jobNew_SiteDetail_Contacts.email AS Email, 
jobNew_SiteDetail.ICCPhone AS ICC_Phone, 
jobNew_SiteDetail.ICCPhoneNote AS ICC_Phone_Note, 
jobNew_SiteDetail.Email AS ICC_Email, 
mstClients.ClientCd, 
mstClients.ClientName, 
jobCtrl_Rollup.RollupCatCd, 
jobCtrl_Rollup.RollupCd

FROM jobNew_HeaderFile 
INNER JOIN jobNew_SiteDetail ON jobNew_HeaderFile.JobCd = jobNew_SiteDetail.JobCd 
INNER JOIN jobNew_SiteDetail_Contacts ON jobNew_HeaderFile.JobCd = jobNew_SiteDetail_Contacts.JobCd 
INNER JOIN jobCtrl_Company ON jobNew_HeaderFile.JobCd = jobCtrl_Company.JobCd 
INNER JOIN mstClients ON jobNew_SiteDetail.ClientCd = mstClients.ClientCd 
INNER JOIN jobCtrl_Rollup ON jobNew_HeaderFile.JobCd = jobCtrl_Rollup.JobCd

WHERE 
jobNew_HeaderFile.JobCd = 990
--AND jobNew_SiteDetail.ActiveStatus = '1'
AND jobCtrl_Rollup.CurrentYN = '1'

GROUP BY
jobNew_HeaderFile.JobCd, 
jobCtrl_Company.Company, 
jobNew_HeaderFile.JobName, 
jobNew_SiteDetail.JobAddr1, 
jobNew_SiteDetail.JobAddr2, 
jobNew_SiteDetail.JobCity, 
jobNew_SiteDetail.JobState, 
jobNew_SiteDetail.JobZip1, 
jobNew_SiteDetail_Contacts.ContactCd,
jobNew_SiteDetail_Contacts.FirstName, 
jobNew_SiteDetail_Contacts.LastName, 
jobNew_SiteDetail_Contacts.Title, 
jobNew_SiteDetail_Contacts.Phone_wk, 
jobNew_SiteDetail_Contacts.Phone_cell, 
jobNew_SiteDetail_Contacts.email, 
jobNew_SiteDetail.ICCPhone, 
jobNew_SiteDetail.ICCPhoneNote, 
jobNew_SiteDetail.Email, 
mstClients.ClientCd, 
mstClients.ClientName, 
jobCtrl_Rollup.RollupCatCd, 
jobCtrl_Rollup.RollupCd

我尝试的聚合语句 #

Select JobCd
      ,FName1 = max( case when RN=1 then FName end)
      ,LName1 = max( case when RN=1 then LName end)
      ,FName2 = max( case when RN=2 then FName end)
      ,LName2 = max( case when RN=2 then LName end)
      ,FName3 = max( case when RN=3 then FName end)
      ,LName3 = max( case when RN=3 then LName end)
 From ( 
        Select JobCd
              ,RN    = row_number() over (partition by JobCd order by [ContactCd])
              ,FName = parsename(replace([FirstName],' ','.'),1)
              ,LName = parsename(replace([LastName],' ','.'),1)
         From  jobNew_SiteDetail_Contacts
      ) A

     Group By JobCd

期望结果 #

内容的提问来源于stack exchange，提问作者ChrsKslr