Generate an ID based on Column Values

Question

I have the following dataset that represents a mapping between a project and activity:

ProjectID  ActivityID
A         A
B         B
B         C
D         D
E         D
M         N

I'd like to calculate an ID, based on the following rules:

a project that maps 1-1 with an activity:
A - A
M - N

one project that maps to multiple activities:
B - B
B - C

one activity that maps to multiple projects:
D - D
E - D

This would generate:

ProjectID  Activity CalculatedID
A         A        1
B         B        2
B         C        2
D         D        3
E         D        3
M         N        4

I hope there's enough info there, any ideas appreciated. I'm particularly interested in seeign a set-based approach.

-- UPDATE: Note on the answers -- I'd describe thE approach taken by @Erwin as a classification of the mappings, in contrast to the solution provided by @mellamokb (that builds on @CodeByMoonlight's solution) that assigns a seqential ID. Both of your solutions have helped me on my way, thanks guys!

Answer 1

It's a little convoluted but it works:

SELECT ProjectID, ActivityID,
DENSE_RANK() OVER(ORDER BY ProjectID) +
DENSE_RANK() OVER(ORDER BY ActivityID) -
ROW_NUMBER() OVER(ORDER BY ProjectID, ActivityID) AS CalculatedID
FROM MyTable

The two uses of DENSE_RANK have the effect of creating an offset against ROW_NUMBER when a repeat of ProjectID or ActivityID occurs.