SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT *
INTO dbo.YourTable
FROM (VALUES(1,'North Office','123 Test Rd.'),
            (2,'   Eastern  ','456 Fake St.'),
            (3,'North Office','789 Data Ln.'),
            (4,'West Office ','111 Western Ave.'),
            (5,'North Office','901 Delta Rd.'))V(SiteId,SiteName, SiteAddress);
5 rows affected

WITH CTE AS(
    SELECT SiteId,
           SiteName,
           COUNT(*) OVER (PARTITION BY SiteID) AS Sites
    FROM dbo.YourTable)
SELECT SiteId,
       SiteName
FROM CTE
WHERE Sites > 1;


SiteId
SiteName


WITH CTE AS(
    SELECT SiteId,
           SiteName,
           COUNT(*) OVER (PARTITION BY SiteName) AS Sites
    FROM dbo.YourTable)
SELECT SiteId,
       SiteName
FROM CTE
WHERE Sites > 1;


SiteId
SiteName


3
North Office

1
North Office

5
North Office


DROP TABLe dbo.YourTable;