HanShi (9/10/2013)
If there are double rows (like the same NAME is entered twice both with the ROLE 'User') the solution of Koen will give false results. The code below will just display the results where a NAME is entered only once as 'Admin' and once as 'User'.
select namefrom Sample
group by name
having sum(case when ROLE = 'Admin' then 1 else 0 end) = 1
and sum(case when ROLE = 'User' then 1 else 0 end) = 1
Pffff, crap in crap out 😀
I believe this to be a more elegant solution:
SELECT Name
FROM [SAMPLE]
GROUP BY Name
HAVING COUNT(DISTINCT [Role]) > 1;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP