K
I don't totally get the purpose of it, but from checking at your query, it looks like you're moving Address, Salary and PromotionDate to different rows, and then you're merging all together.
One option is to avoid that expansion and later merge by doing all together in the same operation:
SELECT a.AccountNo
,a.RunKey
,a.RunDate
,Item = STUFF((SELECT ',' + 'Address,Salary,PromotionDate'
FROM @YourTable b
WHERE a.AccountNo = b.AccountNo AND a.RunKey = b.RunKey AND a.RunDate = b.RunDate
FOR XML PATH('')), 1, 1, '')
,Value = STUFF((SELECT ',' + b.Address + ',' + CAST(b.Salary AS VARCHAR(10)) + ',' + CAST(b.PromotionDate AS VARCHAR(10))
FROM @YourTable b
WHERE a.AccountNo = b.AccountNo AND a.RunKey = b.RunKey AND a.RunDate = b.RunDate
FOR XML PATH('')), 1, 1, '')
FROM @YourTable AS a
GROUP BY a.AccountNo
,a.RunKey
,a.RunDate
Or even easier if you're using SQL 2017 or later (there might be some issues with this version though, if the output of the Item or Value columns is too long):
SELECT a.AccountNo
,a.RunKey
,a.RunDate
,Item = STRING_AGG('Address,Salary,PromotionDate', ',')
,Value = STRING_AGG(a.Address + ',' + CAST(a.Salary AS VARCHAR(10)) + ',' + CAST(a.PromotionDate AS VARCHAR(10)), ',')
FROM @YourTable AS a
GROUP BY a.AccountNo
,a.RunKey
,a.RunDate
Also, make sure there's a clustered index on AccountNo, RunKey, RunDate or at least one covering all the columns included in the query.