How to find matches in a data table in SQL?-CodePudding

I have one assigment.

Question: Do someone know an easy solution? I found no way to solve the question.

Try it: 

DROP TABLE IF EXISTS #Employees;
GO

CREATE TABLE #Employees
(
EmployeeID  INTEGER,
License     VARCHAR(100),
PRIMARY KEY (EmployeeID, License)
);
GO

INSERT INTO #Employees VALUES
(1001,'Class A'),
(1001,'Class B'),
(1001,'Class C'),
(2002,'Class A'),
(2002,'Class B'),
(2002,'Class C'),
(3003,'Class A'),
(3003,'Class D');
GO

My try but it does not work. Do someone have good idea?

SELECT * 
FROM #Employees as e1
LEFT JOIN (SELECT * 
            FROM #Employees 
            WHERE 1 = 1 
              AND EmployeeID = 2002 ) as e2   ON e1.License = e2.License
LEFT JOIN (SELECT * 
            FROM #Employees 
            WHERE 1 = 1 
              AND EmployeeID = 3003 ) as e3   ON e1.License = e3.License
WHERE 1 = 1 
  AND e1.EmployeeID = 1001

CodePudding user response：

If your version of SQL Server supports STRING_AGG function (SQL Server 2017 (14.x) and later), you may perform two levels of aggregation as the following:

SELECT EmpLic, STRING_AGG(EmployeeID, ',') AS EmpsWithSimilarLic
FROM
(
  SELECT EmployeeID,
       STRING_AGG(License, ',')  WITHIN GROUP (ORDER BY License) AS EmpLic
  FROM #Employees
  GROUP BY EmployeeID
) T
GROUP BY EmpLic
HAVING COUNT(*) > 1

See a demo.

CodePudding user response：

  with cte as (

  SELECT EmployeeID,
       STRING_AGG(License, ',') 
       AS lic_agg
  FROM Employees
  GROUP BY EmployeeID ) 

  SELECT STRING_AGG(EmployeeID, ',') as Matches
  FROM cte
  GROUP BY lic_agg
  Having COUNT(*) > 1