I have create stored procedure to select data and result is:
ID NAME EMAIL
1 John [email protected]
2 Sam [email protected]
3 Tom [email protected]
4 Bob [email protected]
5 Tom [email protected]
and I would like to get result like:
ID NAME EMAIL
1 John [email protected]
2 Sam [email protected]
3 Tom [email protected], [email protected]
4 Bob [email protected]
so, how can I do it?
Thanks.
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
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 [email protected] 1
2 Sam [email protected] 1
3 Tom [email protected] 2
4 Bob [email protected] 1
adding
HAVING count(email) > 1
to the end will result in
1 Tom [email protected] 2
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;
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
