Finding Common Groups

Transact-SQL
1

Hi All,

I’ve been out of the SQL game for a while now and am trying to get back into it. I have some data I’m trying to find matching groups of. I’m not even really sure how to describe what I’m looking for.

I have two data points. Teams and people. Each team has one or more people in it, and I want to find any teams that have the same people in them.

Column 1 Column 2
X Adam
X Dana
X Fiona
Y Adam
Y Becky
Y Carl
Y Eric
Z Adam
Z Becky
Z Carl
Z Eric

We can query the data and get the following.

Teams Adam Becky Carl Dana Eric Fiona
X 1 1 1
Y 1 1 1 1
Z 1 1 1 1

We can see that team Y and Z are the same. There are hundreds of teams and a thousand plus people so I’m trying to build a query that will more readily identify the teams that match each other.

2

Here’s a quick and naive way:

DROP TABLE IF EXISTS #;

CREATE TABLE # (
    team   CHAR (1)     NOT NULL,
    member VARCHAR (32) NOT NULL,
    PRIMARY KEY (team, member)
);

INSERT  #
VALUES ('X', 'Adam'),
('X', 'Dana'),
('X', 'Fiona'),
('Y', 'Adam'),
('Y', 'Becky'),
('Y', 'Carl'),
('Y', 'Eric'),
('Z', 'Adam'),
('Z', 'Becky'),
('Z', 'Carl'),
('Z', 'Eric');

WITH   checks
AS     (SELECT   team,
                 CHECKSUM_AGG(binary_checksum(member)) AS check_
        FROM     #
        GROUP BY team),
       ties
AS     (SELECT   check_
        FROM     checks
        GROUP BY check_
        HAVING   count(*) > 1)
SELECT b.team as identical_teams
FROM   ties AS a
       INNER JOIN
       checks AS b
       ON a.check_ = b.check_;

It shows they are identical, but if there’s more than 3 groups it won’t pair them up (A=B, C=D, but A<>C). CHECKSUM_AGG() might not be the best way to determine group uniqueness.

I might have a better variation that does identify them that way but not sure I’ll get to it tonight.