I had the need to do a recursive call that included depth and sort orders. I wanted to reformat the sort orders in a single query without using a cursor or loop.
Turns out there is an easy way with SQL 2005 or higher.
@Folders is a table var from my CTE with Depth and SortOrder in it.
SELECT ID,(ROW_NUMBER() OVER (ORDER BY f.Depth,f.SortOrder,r.SortOrder)) * 100 as SortOrder
FROM someTable r
JOIN @Folders f
ON r.FolderID = f.ID
ORDER BY SortOrder;