Return NULL when distinct values else return value

Below is an example of the type of data I get (collected by different users):

name
surname

Moe
Momo

Moe
Momo

Jack
JAJA

Jack
Jacky

I would like to find when two users have collected different surnames for the same name.

The output I'm trying to get is:

name
surname

Moe
Momo

Jack
NULL

I would see the surname if all users have collected the same, and NULL if there are differences.

I tried searching the internet but I'm not able to describe what I'm searching properly.

I tried a request using CASE, but with no success.

This can be solved using COUNT(DISTINCT ...). Group the results by name. Count distinct last names per first name. If the count differs from 1, show the last name as a null, otherwise show the actual last name e.g. using MAX, like this:

SELECT
  name
, surname = CASE COUNT(DISTINCT surname) WHEN 1 THEN MAX(surname) END
FROM
  dbo.People
GROUP BY
  name
;

You have to apply an aggregate function to surname because the grouping is by name only. Since you only show it when the distinct count is 1, it should not matter much which instance you pick, since they are all the same. MIN would work as well.

StackExchange Database Administrators Q#313815, answer score: 6