SELECT activity, oldSponser, oldSibling, newSponser, start+rn AS newSibling
FROM (SELECT activity, sponsor, sibling, 4, ROW_NUMBER() OVER(ORDER BY activity)
      FROM @activity
      WHERE sponsor <> 4
      GROUP BY activity, sponsor, sibling) a(activity,oldSponser,oldSibling,newSponser,rn)
CROSS APPLY (SELECT MAX(sibling)
             FROM @activity
             WHERE sponsor = 4) b(start);

select activity
, a.sponsor 'OldSponsor'
, sibling 'OldSibling'
, 4 'NewSponsor'
--, '?' 'NewSibling' --first result should be 3, second 4, etc based on max(sibling)+x for group sponsor=4
, ( SELECT MAX(sibling)
    FROM @activity 
    WHERE sponsor = 4 
    GROUP BY sponsor) + 
        ROW_NUMBER() OVER (ORDER BY activity) 'NewSibling'
from (
    SELECT activity, sponsor, sibling
    FROM @activity
    where sponsor <> 4
    group by activity, sponsor, sibling) a