How to check duplicate column values?

Question

I have create stored procedure to select data and result is:

ID   NAME   EMAIL
1    John   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com
4    Bob    bob@asd.com
5    Tom    asc@asd.com

and I would like to get result like:

ID   NAME   EMAIL
1    John   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com, asc@asd.com
4    Bob    bob@asd.com

so, how can I do it?

Thanks.

Answer 1

select 
    id,
    name,
    email
from (
    select
        rn = row_number() over(partition by name order by id asc),
        id,
        name,
        email =  stuff((select ', ' + convert(varchar, t2.email)
                        from @table_var t2 
                        where t1.name = t2.name
                        for xml path(''))
                    ,1,2,'')
    from @table_var t1
    group by t1.id, t1.name
)t
where rn = 1
order by id

Answer 2

GROUP BY is what you're after.

For example

SELECT name, email, count(email)
FROM table
GROUP BY name, email

will return something like

1 John asd@asd.com 1

2 Sam asd@asd.com 1

3 Tom asd@asd.com 2

4 Bob bob@asd.com 1

adding

HAVING count(email) > 1

to the end will result in

1 Tom asd@asd.com 2

Answer 3

Just another way, could help

;WITH cte
AS
(
  SELECT Id
     ,Name
     ,Email
     ,ROW_NUMBER() OVER(PARTITION BY Name,Email ORDER BY Id) AS rowNum
  FROM Table
)
SELECT Id,Name,Email 
FROM cte
WHERE rowNum=1;

Answer 4

A solution is :

select distinct e1.Name,
 (case when e2.Email is null then e1.Email else 
 ( case when e1.Email > e2.Email then e1.Email + ','+ e2.Email else e2.Email + ','+ e1.Email end )
  end ) from MyTable e1 
left join MyTable e2 on e1.Name = e2.Name and e1.Email <> e2.Email