As
Duncan Edwards Jones[
^] mentioned in the comment to the question, you have to repair data in a
CompanyName
field.
At this moment, my best guess is:
DECLARE @tmp TABLE(EmpID INT, EmpName VARCHAR(50), CompanyName VARCHAR(155), CompanyID INT)
INSERT INTO @tmp(EmpID, EmpName, CompanyName, CompanyID)
VALUES(123, 'Josep', 'Kramer Levin Naftalis & Frankel LLP', 468),
(123, 'Josep', 'Thompson Hine LLP', 567),
(801, 'Simon', 'Ogletree Deakins International LLP', 222),
(801, 'Simon', 'Ogletree, Deakins, Nash PC', 916),
(602, 'alen', 'Baker Co Ltd', 732),
(602, 'alen', 'Baker Mcken Ltd', 242)
SELECT EmpID, LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1) AS ShortComanyName
FROM @tmp
GROUP BY EmpID, LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1)
HAVING COUNT(EmpID)=1
Above query returns:
EmpID ShortComanyName
123 Kramer
123 Thompson