populate null value for specific column B if row number partition by column A count >1-CodePudding

Table department contain department id and name. Need to populate as null in name when same department id is more than once.

Dept    Name  
200    ABC  
200    XYZ  
100    DEF

Output

Dept   Name  
200    NULL  
100    DEF

CodePudding user response：

Here is the solution using window function.

select 
    distinct dept ,  
    case when count(*) over (partition by dept) > 1 then NULL 
         else [name] end as [Name]
from dept
order by dept;

CodePudding user response：

Do a GROUP BY. If there's only one row for a Dept, return its name, else NULL is returned.

select Dept, case when count(*) = 1 then max(Name) end
from department
group by Dept