Find any duplicate value in different columns SQL Server

With a model like this you have to go this way:

SELECT id
FROM dbo.TableName
WHERE phone1 = phone2 
OR    phone1 = phone3
OR    phone1 = phone4
OR    phone1 = phone5
OR    phone2 = phone3
OR    phone2 = phone4
OR    phone2 = phone5
OR    phone3 = phone4
OR    phone3 = phone5
OR    phone4 = phone5

If you had another table where you store those phone-numbers which is linked via foreign-key you could do:

SELECT t.id
FROM dbo.TableName t INNER JOIN dbo.PhoneNumbers p
    ON t.ID = p.FkID
GROUP BY id
HAVING COUNT(p.PhoneNumber) <> COUNT(DISTINCT p.PhoneNumber)

One other way is using cross apply with table valued constructor

SELECT id
FROM   Yourtable
       CROSS apply (VALUES ('Phone1',Phone1),
                           ('Phone2',Phone2),
                           ('Phone3',Phone3),
                           ('Phone4',Phone4),
                           ('Phone5',Phone5)) cs(cname, data)
GROUP  BY id,
          data
HAVING Count(1) > 1 

If you are using a Name-Value pair model, then all you have to do is UNPIVOT your table to get it to return data how you want it to look. If you are not using a Name-Value pair table, I would suggest rethinking the design of the table.

You might use UNION ALL:

WITH cte AS (
    SELECT id, phone1 AS phone FROM mytable
     WHERE phone1 IS NOT NULL
     UNION ALL
    SELECT id, phone2 AS phone FROM mytable
     WHERE phone2 IS NOT NULL
     UNION ALL
    SELECT id, phone3 AS phone FROM mytable
     WHERE phone3 IS NOT NULL
     UNION ALL
    SELECT id, phone4 AS phone FROM mytable
     WHERE phone4 IS NOT NULL
     UNION ALL
    SELECT id, phone5 AS phone FROM mytable
     WHERE phone5 IS NOT NULL
)
SELECT id FROM (
    SELECT id, phone, COUNT(*) FROM cte
     GROUP BY id, phone
    HAVING COUNT(*) > 1
) dup;