SQL redact column B value when column A value is too unique-CodePudding

In the table below, I want to use SQL to replace a UserID when a GroupID has <=1 unique UserID's associated with it:

GroupID	UserID
1	123
1	456
1	789
2	987
3	876
3	765

The returned result would look like this:

GroupID	UserID
1	123
1	456
1	789
2	redacted
3	876
3	765

The use case here would be to prevent the ability to identify a single user based on a group. If a group has more than one user, that it considered anonymous enough to display.

Any help here would be appreciated.

CodePudding user response：

Just another option using the window function sum() over()

Example

Select GroupID
      ,UserID   = case when sum(1) over (partition by GroupID) = 1 then 'Redacted' else left(UserID,25) end
 from YourTable

Results

GroupID UserID
1       123
1       456
1       789
2       Redacted
3       876
3       765

CodePudding user response：

You can use aggregate functions partitioned over a window:

select GroupId, 
    case when Min(userid) over(partition by groupid) =  Max(userid) over(partition by groupid) 
      then 'redacted' 
        else Cast(userid as varchar(10)) 
    end as UserId
from t

Note this assumed userId is an integer data type, if it's already a varchar you don't need to cast it.