select duplicate columns in sql server

Question

Table 1

Id    Name
1     xxxxx
1     ccccc
2     uuuuu
3     ddddd

I want to select where the Id have multiple entries with same Id

How to do this?

Answer 1

You can find ids with multiple entries and then use LEFT JOIN/IS NOT NULL pattern to retrieve corresponding data from the original table :

SELECT t1.* 
FROM tbl t1 
LEFT JOIN ( SELECT id 
            FROM tbl 
            GROUP BY id 
            HAVING COUNT(*) > 1) t2 ON t1.id = t2.id 
WHERE t2.id IS NOT NULL

Other possible solutions include using EXISTS or IN clauses instead of LEFT JOIN/IS NOT NULL.

Answer 2

With ranking functions

Y as (
select *, count(*) over (partition by id) counter
from X)
select id, name from Y where counter > 1