I need to find commonality across 3 columns of data. For instance, user 1 and user 2 have the same email. user 2 and user 3 have the same device id. So I want to assign a unique parentuserid of 1 to user 1, 2, and 3. See below for sample data.
DROP TABLE IF EXISTS #usr SELECT 'u1' AS userid, 'email1' AS email, 'device1' AS device INTO #usr UNION ALL SELECT 'u2' AS userid, 'email1' AS email, 'device2' AS device UNION ALL SELECT 'u3' AS userid, 'email99' AS email, 'device2' AS device UNION ALL SELECT 'u4_nomatch' AS userid, 'email77' AS email, 'device77' AS device -- expected results -- new parentuserid userid -- 1 u1 -- 1 u2 -- 1 u3 -- 2 u4_nomatch
This parentuserid = 1 is a set of 3 users, but this could actually be n (50+). I thought maybe cross join with window functions would be needed here or maybe recursive CTE but I haven't had any luck. It's hard to Google this sort of problem.
SQL Server 2017