-- Working Example:
id EId RelatedID relation
2 122233 1 Children
3 122233 2 Children
1 122233 3 Wife
4 122234 1 Children
7 122235 1 Children
6 122235 2 Handicap
5 122235 3 Wife
8 122237 1 Wife
11 122239 1 Children
12 122239 2 Children
13 122239 3 Children
10 122239 4 Handicap
14 122239 5 Z
9 122239 6 Wife
DECLARE @temp table (id INT IDENTITY, EId char(10), RelatedID char(1), relation char(10))
Insert into @temp values ('122233',NULL, 'Wife')
Insert into @temp values ('122233',NULL, 'Children')
Insert into @temp values ('122233',NULL, 'Children')
Insert into @temp values ('122234',NULL, 'Children')
Insert into @temp values ('122235',NULL, 'Wife')
Insert into @temp values ('122235',NULL, 'Handicap')
Insert into @temp values ('122235',NULL, 'Children')
Insert into @temp values ('122237',NULL, 'Wife')
Insert into @temp values ('122239',NULL, 'Wife')
Insert into @temp values ('122239',NULL, 'Handicap')
Insert into @temp values ('122239',NULL, 'Children')
Insert into @temp values ('122239',NULL, 'Children')
Insert into @temp values ('122239',NULL, 'Children')
Insert into @temp values ('122239',NULL, 'Z')
SELECT Id, EId, RelatedId, Relation,
ROW_NUMBER() OVER (PARTITION BY EID
ORDER BY CASE
WHEN relation='Wife' THEN 9999
WHEN relation='Children' THEN 1
WHEN relation='Handicap' THEN 2
WHEN relation='Z' THEN 3
ELSE 4 END
) AS rn
FROM @temp
DECLARE @id INT, @rn INT
DECLARE cr CURSOR FOR
SELECT Id, -- EId, RelatedId, Relation,
ROW_NUMBER() OVER (PARTITION BY EID
ORDER BY CASE
WHEN relation='Wife' THEN 9999
WHEN relation='Children' THEN 1
WHEN relation='Handicap' THEN 2
WHEN relation='Z' THEN 3
ELSE 4 END
) AS rn
FROM @temp
OPEN cr
WHILE 1=1 BEGIN
FETCH NEXT FROM cr INTO @id, @rn
IF @@Fetch _STATUS <> 0 BREAK
UPDATE @temp SET RelatedId = @rn WHERE id = @id
END
CLOSE cr
DEALLOCATE cr
SELECT * FROM @temp ORDER BY EId, RelatedID