I'm trying to generate a table which returns a count for the preceding instances of the foreign key value up to and including the foreign key value on that row. ForeignIDValue is non-nullable. I've tried table variables and common table expressions but it gets long winded and messy. Is there a more elegant and concise way of doing it?
So table A
PrimaryKeyValue ForeignIDValue ProgressiveForeignIDValueCount
15 42 NULL
16 42 NULL
17 43 NULL
18 42 NULL
19 42 NULL
20 42 NULL
24 42 NULL
26 42 NULL
27 42 NULL
29 42 NULL
30 42 NULL
31 42 NULL
32 42 NULL
35 42 NULL
36 42 NULL
37 42 NULL
38 42 NULL
39 42 NULL
40 44 NULL
41 45 NULL
42 46 NULL
43 45 NULL
Needs to become Table B
PrimaryKeyValue ForeignIDValue ProgressiveForeignIDValueCount
15 42 1
16 42 2
17 43 1
18 42 3
19 42 4
20 42 5
24 42 6
26 42 7
27 42 8
29 42 9
30 42 10
31 42 11
32 42 12
35 42 13
36 42 14
37 42 15
38 42 16
39 42 17
40 44 1
41 45 1
42 46 1
43 45 2
CodePudding user response:
SELECT PrimaryKeyValue, ForeignIDValue,
ProgressiveForeignIDValueCount = ROW_NUMBER() OVER
(PARTITION BY ForeignIDValue ORDER BY PrimaryKeyValue)
FROM dbo.[your table name]
ORDER BY PrimaryKeyValue, ProgressiveForeignIDValueCount;
- Example db<>fiddle