declare @tbl table(ProjectID varchar, ActivityID varchar)...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

declare @tbl table(ProjectID varchar, ActivityID varchar);
insert into @tbl values
 ('A', 'A')
,('B', 'B')
,('B', 'C')
,('D', 'D')
,('E', 'D')
,('M', 'N');


WITH p AS (
    SELECT ProjectID FROM @tbl GROUP BY ProjectID HAVING count(*) > 1
    ),a AS (
    SELECT ActivityID FROM @tbl GROUP BY ActivityID HAVING count(*) > 1
    )
SELECT t.*
      ,CASE
         WHEN p.ProjectID  IS NOT NULL
          AND a.ActivityID IS NOT NULL THEN 4 -- n:m (missing in question!)
         WHEN p.ProjectID  IS NOT NULL THEN 2 -- 1:n
         WHEN a.ActivityID IS NOT NULL THEN 3 -- n:1
         ELSE                               1 -- 1:1
       END AS CalculatedID
FROM   @tbl AS t
LEFT   JOIN p ON p.ProjectID = t.ProjectID
LEFT   JOIN a ON a.ActivityID = t.ActivityID
ORDER  BY CalculatedID, t.ProjectID, t.ActivityID

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results