How to display duplicates in SQL only if another column is different?

Question

So say I have this table:

Name	Role
First	Science
First	Math
First	Science
First	Math
Second	Science
Third	Math
Third	Math

I want to display a column of duplicates for Name/Role ONLY if role is different in each group. So the final result should be like this:

Name	Role
First	Science
First	Math

This is the only person that has a different role for the same name (no matter how many times that specific combination is duplicated). That's why even though Third/Math is also duplicated, it doesn't matter because it's the same combination.

I tried doing a CTE as follows:

;with cte as (
Select Name, Role, ROW_NUMBER() over (partition by name order by name) as 'rownum1' 
from U.Users
group by u.name, u.role)

so then select * from cte where rownum > 1 gets me my names of people that have this issue but it doesn't display the duplicate roles for that user. Not sure how I should approach it differently?

If I join the CTE table to the original Users table, I also get the single entries.

Answer 1

So after I posted question I tried this which isn't as elegant as Kurt's answer but did also work:

;with cte as (select name, role, row_number() over (partition by name order by name) rownum

from user_role 
group by name, role)
          
          select distinct user_role.name, user_role.role from user_role 
          join  cte on cte.name=user_role.name and cte.role=user_role.role
          where user_role.name in (select name from cte where rownum =2)

https://www.db-fiddle.com/f/vzRDgBXwYp3VpgNyfn9qzL/2

Answer 2

You can take advantage of the fact that window functions are applied after aggregation:

select name, role
from (
  select name, role, count(1) over (partition by name) c
  from user_role
  group by name, role
) r
where c > 1

https://www.db-fiddle.com/f/vzRDgBXwYp3VpgNyfn9qzL/0

Answer 3

You can try something like this:

WITH cte1 as (
SELECT distinct *
FROM 
table1
),
cte2 as 
(
  Select Name, Role, ROW_NUMBER() over (partition by name order by name) as rnk 
  from cte1 u 
  group by u.name, u.role
 )
      
SELECT * FROM cte2
where name in
(select name
from cte2
WHERE rnk > 1
group by name
)

I used a distinct function to remove any duplicates, then use the ROW_NUMBER() like you to find Names with multiple rows.

db fiddle link